topic Re: Calculate LY & LY-1 value for the selected YYYYMM in slicers in DAX Commands and Tips

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

varung8899 — Tue, 18 Mar 2025 09:28:23 GMT

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.

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

bhanu_gautam — Tue, 18 Mar 2025 09:37:00 GMT

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.

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

varung8899 — Tue, 18 Mar 2025 09:48:52 GMT

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

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

varung8899 — Tue, 18 Mar 2025 09:56:00 GMT

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.

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

bhanu_gautam — Tue, 18 Mar 2025 10:20:05 GMT

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
)
)

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

varung8899 — Tue, 18 Mar 2025 10:31:44 GMT

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

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

lbendlin — Tue, 18 Mar 2025 11:03:56 GMT

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.

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

varung8899 — Tue, 18 Mar 2025 11:20:53 GMT

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.

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

lbendlin — Tue, 18 Mar 2025 12:01:40 GMT

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

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

varung8899 — Tue, 18 Mar 2025 14:24:35 GMT

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)

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

lbendlin — Tue, 18 Mar 2025 19:01:50 GMT

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