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.
Hello there,
I am trying to get the last 12 months' total revenue in a calulcated column, which I can filter with a date slicer. I want a table at the end which shows selected month's revenue and last 12 months' revenue from the selected date.
How to calculate the same if the Dates are the end of each month? I tried below DAX for measure but when date filter is applied, the measure goes blank. The calculated column using DATESYTD gives the same number as Revenue (mentioned in the table below)
Sharing the sample data for reference:
Date | ID | Revenue | Revenue YTD (Column) | Revenue_Ytd (Measure) |
31/07/2018 | 32466 | 5495.42 | 5495.42 | |
31/08/2018 | 32466 | 2815.07 | 2815.07 | |
30/09/2018 | 32466 | 2878.24 | 2878.24 | |
31/10/2018 | 32466 | 2795.92 | 2795.92 | |
30/11/2018 | 32466 | 2818.15 | 2818.15 | |
31/12/2018 | 32466 | 2664.91 | 2664.91 | |
31/01/2019 | 32466 | 2445.28 | 2445.28 | |
28/02/2019 | 32466 | 2262.11 | 2262.11 | |
31/03/2019 | 32466 | 2067.89 | 2067.89 | |
30/04/2019 | 32466 | 2314.28 | 2314.28 | 28557.27 |
31/05/2019 | 32466 | 2261.89 | 2261.89 | 30819.16 |
30/06/2019 | 32466 | 2342.66 | 2342.66 | 33161.82 |
31/07/2019 | 32466 | 2260.47 | 2260.47 | 29926.87 |
31/08/2019 | 32466 | 2281.32 | 2281.32 | 29393.12 |
30/09/2019 | 32466 | 2305.82 | 2305.82 | 28820.7 |
31/10/2019 | 32466 | 2260.63 | 2260.63 | 28285.41 |
30/11/2019 | 32466 | 2333.88 | 2333.88 | 27801.14 |
31/12/2019 | 32466 | 2290.43 | 2290.43 | 27426.66 |
31/01/2020 | 32466 | 2354.21 | 2354.21 | 27335.59 |
28/02/2020 | 32466 | 2370.57 | 2370.57 | 27444.05 |
31/03/2020 | 32466 | 2186.49 | 2186.49 | 27562.65 |
30/04/2020 | 32466 | 2143.56 | 2143.56 | 27391.93 |
31/05/2020 | 32466 | 1987.85 | 1987.85 | 27117.89 |
30/06/2020 | 32466 | 2092.42 | 2092.42 | 26867.65 |
31/07/2020 | 32466 | 2036.24 | 2036.24 | 26643.42 |
31/08/2020 | 32466 | 2070.13 | 2070.13 | 26432.23 |
30/09/2020 | 32466 | 2090.24 | 2090.24 | 26216.65 |
31/10/2020 | 32466 | 1992.35 | 1992.35 | 25948.37 |
30/11/2020 | 32466 | 2046.59 | 2046.59 | 25661.08 |
31/12/2020 | 32466 | 2018.55 | 2018.55 | 25389.2 |
31/01/2021 | 32466 | 2076.07 | 2076.07 | 25111.06 |
28/02/2021 | 32466 | 2089.05 | 2089.05 | 24829.54 |
31/03/2021 | 32466 | 1870.88 | 1870.88 | 24513.93 |
1/04/2021 | 32466 | 2077.57 | 2077.57 | 24447.94 |
Expected result:
Date | ID | Revenue | Revenue_YTD |
31/03/2021 | 32466 | 1870.88 | 24513.93 |
The DAX I used for measure is:
Solved! Go to Solution.
Hi @amitchandak ,
Thank you for the reply!
I resolved the issue by replacing "ALLSELECTED" with "ALL" in my mentioned measure (filter context at play!). And it works as expected with the date slicer.
The modified measure becomes:
@Anonymous , You can not use slicer value in a calculated column, You have to create a measure only
example with date table
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
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.
Hi @amitchandak ,
Thank you for the reply!
I resolved the issue by replacing "ALLSELECTED" with "ALL" in my mentioned measure (filter context at play!). And it works as expected with the date slicer.
The modified measure becomes:
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 |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |