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,
Im trying to create a column that shows the previous 3 months avg. consume forecasted dynamically. Basically the first 3 months doesnt have the average. This will start the calculation in the month of 5/1/2019, will get the previous average of consume% from (2/1/2019,3/1/2019, 4/1/2019) exclude the current month. same with 6/1/2019, will get the previous average of consume% from (3/1/2019,4/1/2019, 5/1/2019) exclude the current month. After getting the average will calculate now the consume forecasted, the formula would be :
(balance% * prev 3 month avg of consume%) +consume%
ex for 5/1/2019 (0.10%*70.20%) +70.86 = 73.93% consume frcst
6/1/2019 (9.73%*72.30%) +60.94 = 67.98% consume frcst
DT Received consume inprocess consume% balance% prev3monthsavg (consume Forecasted%) 2/1/2019 79606 51384 0 64.55% 0.00% 3/1/2019 84194 61211 0 72.70% 0.00% 0% 4/1/2019 92458 67807 0 73.34% 0.00% 0% 5/1/2019 94531 66988 95 70.86% 0.10% 73.93% 6/1/2019 29832 18181 2903 60.94% 9.73% 67.98%
By the way, I'm new to this forum and DAX fumctionality. Thank you in Advance.
Hi JWick,
My advise would be to incorporate a date calendar into the report. I personally use the one created by Avi Singh which can be downloaded at http://www.learnpowerbi.com/bonus-download (You need to sign up to his newsletter first).
You can view his youtube channel for a tutorial on how he builds the calendar but it will help you as it dynamically updates based on the present date. Using the month offset in his calendar you could then filter you sum to previous months.
e.g. your measure would be:
prev 3 month avg consume =
(Usual Sum) (Filtering only for the past 3 months, this month excluded)
CALCULATE(*YOUR CALCULATION*, AND(Calendar[monthOffset] < 0 , Calendar[monthOffset] > -4))
I hope that helps.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |