SAMEPERIODLASTYEAR doesn't work with Interval time slicer

[Anonymous]

Hello,

I am using the DAX function "SAMEPERIODLASTYEAR" to compare current units with the same period of the previous year and I want to use a "between" date interval filter.

When I include the previous year and this year it works perfectly for the current year data:

The problem is that if I select only the year 2020 in the slicer it doesn't return the data for the year 2019 in the "sameperiodlastyear" column:

Is there any way for this column to work using this kind of date range filter?

Thank you

[Anonymous]

Please learn how to correctly use time-intel functions in DAX:

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Advice: DO NOT EVER USE THE AUTOMATICALLY CREATED DATE HIERARCHY. Create your own. You'll then be safe.

Best
D

[nandukrishnavs]

@Anonymous 

Because you are filtering the dates, so your last year rows will be removed from the context. 

You can use ALL() or ALLEXCEPT() functions to handle this situation. Example https://community.powerbi.com/t5/Desktop/SAMEPERIODLASTYEAR-with-filter/td-p/604118

Refer to this https://www.sqlbi.com/blog/marco/2010/04/05/all-allexcept-and-values-in-dax/

Or share your current logic and sample data

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

Hi @nandukrishnavs 

My way to calculate the units in the previous period is:

units previous period = CALCULATE(SUM('table1'[units]);SAMEPERIODLASTYEAR('Table1'[Datetime].[Date]))

I have cheked your links but i still dont understand the way to do it. How i supose to use ALL or ALLEXCEPT in this function?

Thank you

[nandukrishnavs]

@Anonymous 

Try this 

units previous period =
CALCULATE (
    SUM ( 'Table1'[units] ),
    ALL ( 'Table1'[Datetime] ),
    SAMEPERIODLASTYEAR ( 'Table1'[Datetime].[Date] )
)

This should work.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

Thank you for your response @nandukrishnavs and @Anonymous  . I have used your formula including the ALL and it does not work perfectly:

Although the previous day's values are collected well when I filter for the last month of 2017, I need the previous year column (the last one in the capture) and the total to be filtered by the date filtered as well. However, with that formula neither the rows nor the total of the previous period are filtered.

Is there any way to solve it?

Thank you very much in advance