Forum Discussion

2366's avatar
2366
Icon for Helper I rankHelper I
2 years ago
Solved

SamePeriodLastYear not working as expected

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 jus...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

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:

  1. 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.