Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have data that looks like this:
Cut-off Date | Aux_Date | Category | Sales |
23.01.2018 | 01.01.2018 | A | 1200 |
24.01.2019 | 01.01.2019 | A | 8900 |
25.01.2020 | 01.01.2020 | A | 6400 |
23.01.2018 | 01.01.2018 | B | 1100 |
24.01.2019 | 01.01.2019 | B | 5900 |
25.01.2020 | 01.01.2020 | B | 2100 |
23.01.2018 | 01.01.2018 | C | 4800 |
24.01.2019 | 01.01.2019 | C | 2600 |
25.01.2020 | 01.01.2020 | C | 1300 |
For a selected Cut-off Date, I want to show the sum of sales for both the selected date and the date one year prior. My issue now is that I can't just use SAMEPERIODLASTYEAR on the Cut-off Date, since the exact day changes from year to year (in 2018, it's the 23rd; in 2019, it's the 24th). I figured I could just add an auxiliary column that contains the first of each month rather than the actual cut-off date (see column Aux_Date). I built two simple measures:
Sum_Sales = SUM(Data[Sales])
and
Sum_Sales_LY = CALCULATE([Sum_Sales], SAMEPERIODLASTYEAR(Data[Aux_Date])).
But when I put these on my dashboard, Sum_Sales_LY will show (Blank), if I filter on Cut-off Date:
It works fine when I filter on Aux_Date, but that's not what I want:
Is there any way to utilize SAMEPERIODLASTYEAR without having to switch to Aux_Date as filter?
Maybe I'm struggling because I can't quite wrap my head around as to why SAMEPERIODLASTYEAR is a valid input for the <filter> argument of the CALCULATE formula. The documentation suggests that SAMERPERIODLASTYEAR returns a single column of data (i.e. the dates incremented by -1 year), and I don't understand how that serves as a filter to anything.
Solved! Go to Solution.
I actually found the solution myself. Changing the measure to the following does the trick:
I actually found the solution myself. Changing the measure to the following does the trick:
@DanielM16 , Please use the date table in all such cases
Year behind Sales = CALCULATE([Sum_Sales],dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE([Sum_Sales],SAMEPERIODLASTYEAR('Date'[Date]))
Refer to my video why TI fails: https://www.youtube.com/watch?v=OBf0rjpp5Hw
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |