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 am using the formula below to calculate a rolling 3 month total but I want to exclude the current month. So for April I want to sum up Jan - March. Currently the formula sums Feb - Apr. How can I modify the formula. Thanks!!!
Jan 150
Feb 200
Mar 300
Apr 400
Rolling 3 Month Total = CALCULATE(KPI_Finance_Actual_Budget[Actual Profit],DATESINPERIOD(KPI_Finance_Actual_Budget[FullDate],LASTDATE(KPI_Finance_Actual_Budget[FullDate]),-3, MONTH))
Solved! Go to Solution.
Maybe this:
Rolling 3 Month Total = CALCULATE ( KPI_Finance_Actual_Budget[Actual Profit], DATESINPERIOD ( KPI_Finance_Actual_Budget[FullDate], LASTDATE ( PREVIOUSMONTH ( KPI_Finance_Actual_Budget[FullDate] ) ), -3, MONTH ) )
Although if "KPI_Finance_Actual_Budget[FullDate]" doesn't have a consecutive range of all dates you may get some strange results. Best to use a separate calendar table.
What it KPI_Finance_Actual_Budget[Actual Profit] in your original question?
Hi ,
i need simillar help on my data
i have colums like
Period divsion segment net
201501 a 1 12332
201502 b 2 21233
.. ... .... ......
201812 a 2 312333
I need to calcuate avg net value (from 201701 to 201712) for each division and segment by skipping recent 12 periods say (201801 to 201812)
your help would be appreciated.
Thanks
Maybe this:
Rolling 3 Month Total = CALCULATE ( KPI_Finance_Actual_Budget[Actual Profit], DATESINPERIOD ( KPI_Finance_Actual_Budget[FullDate], LASTDATE ( PREVIOUSMONTH ( KPI_Finance_Actual_Budget[FullDate] ) ), -3, MONTH ) )
Although if "KPI_Finance_Actual_Budget[FullDate]" doesn't have a consecutive range of all dates you may get some strange results. Best to use a separate calendar table.
That worked! Thanks for your help.
If you happen to have a problem with consecutive days , you can use the solution below leveraging month numbers in your calendar dimension.
= CALCULATE( [Sales Amount], FILTER( ALL('Date'), 'Date'[Month Number] > MAX('Date'[Month Number]) - 4 && 'Date'[Month Number] <= MAX('Date'[Month Number])-1 ) , VALUES('Date'[Calendar Year]) )
Thanks, Nick -
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |