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.
Hi,
I have used Quick Measure Rolling Average to create 12m rolling sum (replaced the averagex with sumx in the DAX). My problem is that currently on my report I have filtered months 1-4 from January because these are the months that are fully closed this year but the rolling 12m does not take into account the previous year's figures at all because those are not filtered.
How could I solve this?
Example current results and wanted results:
- Revenue is 100 per month and we have results for 1/2018-4/2019.
- Selected months are 1-4/2019
- Rolling 12m revenue with current DAX: 1/2019 = 100, 2/2019 = 200, 3/2019 = 300, 4/2019 = 400
- Rolling 12m revenue what we want: 1/2019 = 1200, 2/2019 = 1200, 3/2019 = 1200, 4/2019 = 1200
Current DAX:
Solved! Go to Solution.
Hi @FatherTheWizard ,
You can try create measures like DAX below, assuming [External Revenue (ACT)]= SUM('(dim) Date'[revenue])
Period End = LASTDATE('(dim) Date'[date])
Period Start= FIRSTDATE( DATESINPERIOD('(dim) Date'[date], [Period End], -12, MONTH))
External Revenue L12M (ACT) =CALCULATE([External Revenue (ACT)],DATESBETWEEN ( '(dim) Date'[date], [Period Start], [Period End] ))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @FatherTheWizard ,
Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case.
Best regards
Amy Cai
Hello @v-xicai ,
I have the same issue but on a rolling average ; I've triend your solution on a rolling average but it does not work, is that normal ?
Alice
Hi @FatherTheWizard ,
You can try create measures like DAX below, assuming [External Revenue (ACT)]= SUM('(dim) Date'[revenue])
Period End = LASTDATE('(dim) Date'[date])
Period Start= FIRSTDATE( DATESINPERIOD('(dim) Date'[date], [Period End], -12, MONTH))
External Revenue L12M (ACT) =CALCULATE([External Revenue (ACT)],DATESBETWEEN ( '(dim) Date'[date], [Period Start], [Period End] ))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |