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 all,
I am trying to create a Column using DAX that calculates a rolling 8-week average based on a parameter/measure with multiple filters depending on the values of other columns.
Data Structure:
There is also a parameter for the current week number in cell K2:
The column Forecast is what I am trying to create using DAX.
What it does is:
- If the week number is less than or equal to the Current Week, just copy the value under "Actual" (Column E)
- Else, get the average of "Actual" (Column E) of the 8 weeks prior to the current week (if Current Week = 38, get average of weeks 30-37), subject to having the same FY, same KPI, and same Owner with the current row.
In Excel, the correct formula for Row #2 would be:
=IF(
B2<=$K$2,
E2,
AVERAGEIFS(
E:E,
B:B,"<"&$K$2,
B:B,">="&$K$2-8,
D:D,D2,
C:C,C2,
A:A,A2
)
)
I am really struggling to find the right DAX syntax because I can't seem to figure out how to pass one of the values of the current row into the DAX filters. I tried doing something like this (without the IF statement) but obviously it's not working:
I have uploaded the sample Excel and PBIX files for easier reference. You can see how the correct calculation is done in Excel.
- PBIX: https://drive.google.com/file/d/1TqT_xKZg3s7sSyZJr4yrQPtt-iW4mU8I/view?usp=sharing
Any help will be greatly appreciated!
Cheers,
Andrew
@Anonymous , For week , I usually create a week rank in date table and use that
New column
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures
Last 8 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Hi amitchandak,
Thanks for the response. I already have the week numbers so no need to create a weekrank function.
I tried to adapt your code, and I think we're getting somewhere.
Forecast DAX = CALCULATE(AVERAGE('Sample'[Actual]),FILTER(ALL('Sample'),'Sample'[Week #]>=[Current Week]-8 && 'Sample'[Week #]<[Current Week]))
Output:
It still lacks the additional conditions that I mentioned earlier. I'm trying to do something like this, but I'm not finding the right syntax:
Forecast DAX = CALCULATE(AVERAGE('Sample'[Actual]),FILTER(ALL('Sample'),'Sample'[Week #]>=[Current Week]-8 && 'Sample'[Week #]<[Current Week]) && 'Sample'[FY]=rowvalue(FY) && 'Sample'[KPI]=rowvalue(KPI) && 'Sample'[Owner]=rowvalue(Owner))
I know that rowvalue is not a real function, but it demonstrates what I'm trying to do. There are 3 additional filters for me to use so that the AVERAGE function only calculates if the FY, KPI, and Owner columns match the current row.
I also tried this
Forecast DAX = CALCULATE(AVERAGE('Sample'[Actual]),FILTER(ALL('Sample'),'Sample'[Week #]>=[Current Week]-8 && 'Sample'[Week #]<[Current Week]) && 'Sample'[FY]=earlier('Sample'[FY]) && 'Sample'[KPI]=earlier('Sample'[KPI]) && 'Sample'[Owner]=earlier('Sample'[Owner]))
But I am getting this error:
A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
It's not very helpful. I just added conditions via && and didn't add any FILTER functions, so I don't know what's wrong.
Would you be able to help?
Thanks,
Andrew
I found the solution!
Forecast DAX = IF('Sample'[Week #]<=[Current Week],'Sample'[Actual],CALCULATE(AVERAGE('Sample'[Actual]),FILTER(ALL('Sample'),'Sample'[Week #]>=[Current Week]-8 && 'Sample'[Week #]<[Current Week]),FILTER(all('Sample'),'Sample'[FY]=earlier('Sample'[FY])),FILTER(ALL('Sample'),'Sample'[KPI]=EARLIER('Sample'[KPI])),FILTER(ALL('Sample'),'Sample'[Owner]=earlier('Sample'[Owner]))))
So I learned the following:
Thank you amitchandak, your reply might not have been the solution, but it was helpful for me to think this through!
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |