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?