topic Re: DAX formula to calculate rolling X average based on parameter & values from other columns in DAX Commands and Tips

DAX formula to calculate rolling X average based on parameter & values from other columns

Anonymous — Mon, 13 Sep 2021 13:33:39 GMT

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.

- XLSX: https://docs.google.com/spreadsheets/d/1fYG1dRtkthvLhxMTDXxbx1x_VmoVxf0Y/edit?usp=sharing&ouid=111734964497020988818&rtpof=true&sd=true

- PBIX: https://drive.google.com/file/d/1TqT_xKZg3s7sSyZJr4yrQPtt-iW4mU8I/view?usp=sharing

Any help will be greatly appreciated!

Cheers,

Andrew

Re: DAX formula to calculate rolling X average based on parameter & values from other columns

amitchandak — Mon, 13 Sep 2021 14:24:56 GMT

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

Re: DAX formula to calculate rolling X average based on parameter & values from other columns

Anonymous — Mon, 13 Sep 2021 15:12:43 GMT

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

Re: DAX formula to calculate rolling X average based on parameter & values from other columns

Anonymous — Mon, 13 Sep 2021 15:25:58 GMT

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:

You can't just keep adding &&s as additional conditions. You have to create separate FILTER arguments for each. Not very intuitive, but good to know.
EARLIER actually is the function to reference the current row's value, not the previous one. Again, not very intuitive, but now I know better.

Thank you amitchandak, your reply might not have been the solution, but it was helpful for me to think this through!