DAX formula to calculate rolling X average based on parameter & values from other columns
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, AndrewRow Number in DAX
Hi, My SQL query is this ; SELECT CommitteeName, AllotmentMainID, ApprovedTotalLimit, ROW_NUMBER() OVER(PARTITION BY CommitteeName,AllotmentMainID ORDER BY ApprovedTotalLimit DESC) AS RN FROM Allotment; I want to write in Dax. How to write Row Number in Dax? Please Help. Thanx 🙂
