topic Re: SAMEPERIODLASTYEAR not working with filters in DAX Commands and Tips

SAMEPERIODLASTYEAR not working with filters

Anonymous — Thu, 08 Jul 2021 05:39:35 GMT

I created a measure to have de Sum of Last Year:

Vorjahr = CALCULATE(SUM(Tabelle1[Anzahl]),SAMEPERIODLASTYEAR(Tabelle1[Date]))

When i have the following table, everything is working:

But if I add a dimension to the table, which i filter, then the numbers in the column Vorjahr aren't corret:

Without the dimension in the table, just filtering it, the numbers are correct:

Can somebody help me with this? How can i add dimensions in the table and filter them so that the numbers are still correct?

Thank you

Greeting Karen

Re: SAMEPERIODLASTYEAR not working with filters

amitchandak — Thu, 08 Jul 2021 07:40:14 GMT

Anonymous , Make sure you use date table for that. Also you date in table should not have timestamp

Vorjahr = CALCULATE(SUM(Tabelle1[Anzahl]),SAMEPERIODLASTYEAR(Date[Date]))

Vorjahr = CALCULATE(SUM(Tabelle1[Anzahl]),dateadd(Date[Date],-1,year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Why Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw

Re: SAMEPERIODLASTYEAR not working with filters

Anonymous — Thu, 08 Jul 2021 09:16:03 GMT

Thank you for your useful tip. Now i created a datetable, and it's more or less working, the only thing is, that when i now filter via Jahr (from the "basistable") then which is dependent on the scenario (Ist = 2010 - 2020, Reference scenario = 2020-2029) I still get the 2021 in the table in which I have the year from the date_tbl. can this still be excluded somehow? If I take the year Basistbelle (tabelle 1), the Measures are no longer correct

Thank you, best Regards

Re: SAMEPERIODLASTYEAR not working with filters

amitchandak — Thu, 08 Jul 2021 09:52:07 GMT

Anonymous , If you are using date table and time intelligence. Then take all time-related tables from the date table, not from the fact table.

The year should come from date table if I got it correctly.