Calculate LY & LY-1 value for the selected YYYYMM in slicers

[varung8899]

Hello Team,

I have a dim_calendar and fact table. I have used slicers for columns Year(2024) single select & YYYYMM (202401) multi select.
I have YYYYMM column in fact table which is related to dim_calendar. I need to calculate sum of Sales for LY. If I chose 202401, then sales for 202301 should be returned and if there are multi selections then sum of respective months should be displayed. How to achieve this ? If no selection is made then sum of sales for selected year should be displayed. I have also created YYYYMM-1 (202301) & YYYYMM-2 (202201) columns in Dim_calendar. Immediate response would be much appreciated. Thanks. 

[bhanu_gautam]

@varung8899 Default to Sum of Sales for Selected Year if No Selection: If no YYYYMM is selected, the measure should default to summing the sales for the entire selected year.

DAX
LY Sales =
VAR SelectedYearMonths = VALUES(dim_calendar[YYYYMM])
VAR SelectedYear = SELECTEDVALUE(dim_calendar[Year])
VAR LYYearMonths =
SELECTCOLUMNS(
FILTER(
dim_calendar,
dim_calendar[YYYYMM] IN SelectedYearMonths
),
"LYYYYYMM", dim_calendar[YYYYMM-1]
)
RETURN
IF(
ISBLANK(SelectedYearMonths),
CALCULATE(
SUM(fact_table[Sales]),
dim_calendar[Year] = SelectedYear - 1
),
CALCULATE(
SUM(fact_table[Sales]),
dim_calendar[YYYYMM] IN LYYearMonths
)
)

This DAX measure will dynamically calculate the sum of sales for the previous year based on the selected YYYYMM values. If no YYYYMM is selected, it will default to summing the sales for the entire previous year.

[varung8899]

Thank you. But I receive error stating a table of multiple values was supplied where a single value was expected. Pls advise.

[bhanu_gautam]

@varung8899 , This must be due to Selected value

LY Sales =
VAR SelectedYearMonths = VALUES(dim_calendar[YYYYMM])
VAR SelectedYear = SELECTEDVALUE(dim_calendar[Year])
VAR LYYearMonths =
SELECTCOLUMNS(
FILTER(
dim_calendar,
dim_calendar[YYYYMM] IN SelectedYearMonths
),
"LYYYYYMM", dim_calendar[YYYYMM-1]
)
RETURN
IF(
ISBLANK(SelectedYearMonths),
CALCULATE(
SUM(fact_table[Sales]),
dim_calendar[Year] = SelectedYear - 1
),
CALCULATE(
SUM(fact_table[Sales]),
dim_calendar[YYYYMM] IN LYYearMonths
)
)

[varung8899]

This is the same formula as before and it does not work unfortunately. @lbendlin Could you please provide your expert advise ? 

[lbendlin]

A calendar dimension table must be based on dates. Your fact table needs to be modified to contain a date field ( first day of month, for example).  Once you join the tables on the date you can use regular time intelligence functions like SAMEPERIODLASTYEAR.

[varung8899]

@lbendlin I have created date column in Fact table now and related it to Dim_calendar. The relation appears as inactive (dotted line) since I already have YYYYMM columns in relationship. Could you please help me frame the DAX ? I am particularly interested for value when single & multiple values are selected in YYYYMM slicer. 

[lbendlin · Accepted solution]

Remove or inactivate the YYYYMM relationship and activate the date relationship. Use standard DAX.

[varung8899]

Thank you @lbendlin . Found the solution using below code. Please help to find LY-1 Sales if you know since I don't see a direct function available for that. 

Sales_LY =

VAR SelectedYearMonths = SELECTEDVALUE(dim_calendar[YYYYMM])

VAR SelectedYear = SELECTEDVALUE(dim_calendar[Year])

var _SPLY = SAMEPERIODLASTYEAR(Dim_Calendar[Date])

VAR _Output =

    CALCULATE

    (

        SUM(FactData[Value]),

        FactData[Data] = "Mercedes S Class",

        FactData[Product] IN {"Vehicle"},

    _SPLY

    )

return IF (ISBLANK( _Output),0, _Output)

[lbendlin]

Remember that SAMEPERIODLASTYEAR is syntax sugar for DATEADD(x,-12,MONTH). So for LY-1 use SAMEPERIODLASTYEAR(SAMEPERIODLASTYEAR)) or simpler DATEADD(x,-24,MONTH)

[varung8899]

Also please note that YYYYMM & related LY columns are in Number format. Below is sample data when I select 2024 and 202401 in slicers. Sum column is from fact table.