Function 'SAMEPERIODLASTYEAR' expects a contiguous selection Issue

Viewed 2933

I have a tabular data model in Visual Studio, I made a formula to get last year's sales over a given period:

Selected Measure LY:= CALCULATE([Selected Measure], SAMEPERIODLASTYEAR('date' [date]))

I made a formula to have the decomposition of all the months in the 'date' table:

FY_Month='date' [FY Month No]&'date' [Month_label]

And:

FY_Month_No=format(if('date'[month_no]>6,'date'[month_no]-6,'date'[month_no]+6), "00")

Month_label : all the months of the year

The "Selected Measure LY" display works fine when I have all months selected but when I remove one I get this error message:

Calculation error in "Selected Measure LY": the "SAMEPERIODLASTYEAR" function expects a contiguous selection when the date column comes from a tabkle on side 1 of a bidirectional relationship.

Between my 'date' table and my sales table I have a 1/N relationship, I tried to change this relationship too but the error always comes back.

I looked for solutions on forums and I found this but I don't find the same numbers as with my first formula:

Selected Measure LY:=
CALCULATE([Selected Measure],
    FILTER (
        ALL ( 'date' ),
        YEAR ( 'date'[date] ) = YEAR ( TODAY () )
            && 'date'[date] <= TODAY ()
    )
)

My code for Selected Measure:

Selected Measure:=
VAR hasFilter =
    HASONEFILTER ( 'Measure Selection'[Measure Name] )
VAR selMeas =
    SELECTEDVALUE ( 'Measure Selection'[Measure Name], BLANK () ) /*SWITCH works slow, so using many IFs*/
VAR preCalc =
    IF (
        selMeas = "Units",
        [Sku Piece Quantity],
        IF (
            selMeas = "Net Sales",
            [Euro Net Amount],
            IF (
                selMeas = "Gross Sales",
                [Euro Gross Amount],
                IF (
                    selMeas = "Average Wholesale",
                    [Average_Wholesale],
                    IF (
                        selMeas = "Average Units",
                        [Average Units],
                        IF (
                            selMeas = "No of Styles",
                            [Style Count],
                            IF (
                                selMeas = "No of Colours",
                                [Colour Count],
                                IF (
                                    selMeas = "ROD by Style",
                                    [ROD by Style],
                                    IF (
                                        selMeas = "Standard Cost",
                                        [Sum_Cost_Base],
                                        IF (
                                            selMeas = "Net Margin",
                                            [Net_Margin],
                                            IF (
                                                selMeas = "Gross Margin",
                                                [Gross_Margin],
                                                IF ( selMeas = "ROD by Colour", [ROD by Colour], BLANK () )
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
VAR calc =
    IF ( hasFilter, preCalc, BLANK () )
RETURN
    calc

How can I correct it?

0 Answers
Related