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

[Anonymous]

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=11173...

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

Any help will be greatly appreciated!

Cheers,

Andrew

[amitchandak]

@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

[Anonymous]

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

[Anonymous]

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!