topic Re: SAMEPERIODLASTYEAR doesn't work with Interval time slicer in DAX Commands and Tips

SAMEPERIODLASTYEAR doesn't work with Interval time slicer

Anonymous — Mon, 25 May 2020 10:13:33 GMT

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

Re: SAMEPERIODLASTYEAR doesn't work with Interval time slicer

nandukrishnavs — Mon, 25 May 2020 10:46:06 GMT

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 🙂

Re: SAMEPERIODLASTYEAR doesn't work with Interval time slicer

Anonymous — Mon, 25 May 2020 13:25:14 GMT

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

Re: SAMEPERIODLASTYEAR doesn't work with Interval time slicer

nandukrishnavs — Mon, 25 May 2020 13:46:21 GMT

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 🙂

Re: SAMEPERIODLASTYEAR doesn't work with Interval time slicer

Anonymous — Tue, 26 May 2020 01:51:15 GMT

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

Re: SAMEPERIODLASTYEAR doesn't work with Interval time slicer

Anonymous — Tue, 26 May 2020 12:22:06 GMT

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