topic Re: SamePeriodLastYear not working as expected in DAX Commands and Tips

SamePeriodLastYear not working as expected

2366 — Mon, 18 Dec 2023 20:10:13 GMT

Hi All,

I noticed something strange with my data model today and I am hoping someone can please explain the reason behind this behaviour and how to fix it?

I have a simple data model with just DimDate as a table with records filtered up to current date.

In my data model, I have a measure

date ranges = Calculate ( Min(date) & "-" & max(date), datesmtd(Dimdate[Date]))

the above measure returns the expected output I.e

01/12/2023 - 18/12/2023

when I change the above dax to

Calculate ( Min(date) & "-" & max(date), sameperiodlastyear(datesmtd(Dimdate[Date])))

I get the result 01/12/2022 - 31/12/2022 which I don't understand.

if I use a date slicer and select 17/12/2023 as my filter the measure then works as expected 01/12/2022 - 17/12/2022

could you please explain why and also how to fix this?

Re: SamePeriodLastYear not working as expected

123abc — Tue, 19 Dec 2023 05:39:35 GMT

It seems like you're encountering an issue with the SAMEPERIODLASTYEAR function in your DAX measure. The behavior you're describing suggests that the time intelligence functions might be affected by the filter context in your report.

When you use DATESMTD(Dimdate[Date]) in your original measure, it calculates the month-to-date period based on the filter context of the report. However, when you wrap it with SAMEPERIODLASTYEAR, it might not be behaving as expected due to the specific context in which it's being evaluated.

To ensure that SAMEPERIODLASTYEAR works correctly, you should create a separate table or column for your date calculations, and then use that table or column in your measure. This helps to control the filter context explicitly.

Here's an example of how you can modify your DAX:

Create a new calculated column in your DimDate table to store the month-to-date value:

DAX:

MTDDate = DATESMTD(Dimdate[Date])

Now, modify your measure to use this new column:

DAX:

date ranges = CALCULATE(MIN(Dimdate[Date]) & "-" & MAX(Dimdate[Date]), Dimdate[MTDDate])

Finally, use SAMEPERIODLASTYEAR with the new column:

DAX:

date ranges LY = CALCULATE(MIN(Dimdate[Date]) & "-" & MAX(Dimdate[Date]), SAMEPERIODLASTYEAR(Dimdate[MTDDate]))

By doing this, you ensure that the context is well-defined, and the time intelligence functions operate on the correct set of dates. This approach should help resolve the issue you're facing with SAMEPERIODLASTYEAR.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: SamePeriodLastYear not working as expected

Anonymous — Wed, 20 Dec 2023 08:13:13 GMT

Hi, 2366

May I ask if this is the expected output you are looking for? Based on your description, the SamePeriodLastYear function is not returning the expected result in the data model. Here is what might be happening:

1.When you use ‘DATESMTD(DimDate[Date])’, it returns the dates from the start of the month to the current date in the month.

2.When you wrap ‘SamePeriodLastYear’ around ‘DATESMTD’, it attempts to find the same period last year. However, if the current month is not complete, it may return the full month from the previous year.

To fix this issue, you can create a measure that checks if the current period is complete and then apply ‘SamePeriodLastYear’ accordingly. Here is how you can modify your measure:

Measures:

Date Ranges1 = VAR CurrentPeriod = DATESMTD ( DimDate[Date] ) VAR LastYearPeriod = SAMEPERIODLASTYEAR ( CurrentPeriod ) VAR MinDateCurrentPeriod = MINX ( CurrentPeriod, DimDate[Date] ) VAR MaxDateCurrentPeriod = MAXX ( CurrentPeriod, DimDate[Date] ) VAR MinDateLastYear = MINX ( LastYearPeriod, DimDate[Date] ) VAR MaxDateLastYear = IF ( MAX ( DimDate[Date] ) = TODAY (), MaxDateCurrentPeriod - 1, MAXX ( LastYearPeriod, DimDate[Date] ) ) RETURN CALCULATE ( MIN ( DimDate[Date] ) & "-" & MAX ( DimDate[Date] ), LastYearPeriod )

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum