Hi community i am still looking for the correct Dax Formula for the following excercise, is the only one left me to do I have bieng different rolling average without success, ill attach the logic of the excersice in green is the elements that i have in my model, in blue is the diserable result that i need populated in the same column, and in yelow the logic of the calculation please is the last thing I need to complete my project, I ll really apreciate it
Hi Jamc
Click here to download an example solution
Add a index column because W1, W11, W12, !3 will sort before W2.
Then add DAX measure ...
Result =
VAR CurrentWeek = SELECTEDVALUE(Facts[Index]) // gets the row week
RETURN
// get the average for all weeks >= the current week
// get the average for all weeks >= the current week
CALCULATE(
AVERAGE(Facts[Sales]),
ALL(),
Facts[Index] >= CurrentWeek
)
I am an unpaid Power BI volunteer. Please click the thumbs up if you like me trying to help. Then click solved if this fixes your problem. Thank you.
Warm regards, speedramps
Once you convert the Week column to integer (for example, with Text After Delimiter "W"), you can use a DAX measure like this one.
WkAvgSales =
VAR vThisWeek =
MIN( WeeklySales[Week] )
VAR vResult =
CALCULATE( AVERAGE( WeeklySales[SALES] ), WeeklySales[Week] >= vThisWeek )
RETURN
vResult
Pat
Hi it was very Helpfull
But now I was asked for more compexity, I need to organize it by product and by date
| Item | WEEK | Sales | Desirrable result |
| Product A | 4/18/2022 0:00 | 10 | 40 |
| Product A | 4/25/2022 0:00 | 15 | 42.5 |
| Product A | 5/2/2022 0:00 | 20 | 45 |
| Product A | 5/9/2022 0:00 | 25 | 47.5 |
| Product A | 5/16/2022 0:00 | 30 | 50 |
| Product A | 5/23/2022 0:00 | 35 | 52.5 |
| Product A | 5/30/2022 0:00 | 40 | 55 |
| Product A | 6/6/2022 0:00 | 45 | 57.5 |
| Product A | 6/13/2022 0:00 | 50 | 60 |
| Product A | 6/20/2022 0:00 | 55 | 62.5 |
| Product A | 6/27/2022 0:00 | 60 | 65 |
| Product A | 7/4/2022 0:00 | 65 | 67.5 |
| Product A | 7/11/2022 0:00 | 70 | 70 |
| Product B | 4/18/2022 0:00 | 65 | 35 |
| Product B | 4/25/2022 0:00 | 60 | 32.5 |
| Product B | 5/2/2022 0:00 | 55 | 30 |
| Product B | 5/9/2022 0:00 | 50 | 27.5 |
| Product B | 5/16/2022 0:00 | 45 | 25 |
| Product B | 5/23/2022 0:00 | 40 | 22.5 |
| Product B | 5/30/2022 0:00 | 35 | 20 |
| Product B | 6/6/2022 0:00 | 30 | 17.5 |
| Product B | 6/13/2022 0:00 | 25 | 15 |
| Product B | 6/20/2022 0:00 | 20 | 12.5 |
| Product B | 6/27/2022 0:00 | 15 | 10 |
| Product B | 7/4/2022 0:00 | 10 | 7.5 |
| Product B | 7/11/2022 0:00 | 5 | 5 |
I was trying withs this formula but it didnt return the result that I wanted:
RR = VAR RRWEEK =
MIN('TRIAL VERSION'[Week] ) VAR RESULT = CALCULATE(AVERAGE('TRIAL VERSION'[SALES]), FILTER(ALLSELECTED('TRIAL VERSION'), 'TRIAL VERSION'[PRODUCT] = MIN('TRIAL VERSION'[PRODUCT] ) && 'TRIAL VERSION'[Week]<=RRWEEK)) RETURN RESULT
Thanks
Ashish
It looks like Jamc has manually calulated the desired column,
by getting the average for
WK11 to WK13
WK12 to WK13
WK13 to WK13
etc to etc
Speedramps and Mahoneypat both provided viable solutions to get the result using PBI.
Hi ASHISH
I leave the logic of the average calculation in an add last column
| Item | WEEK | Sales | Desirrable result | AVERAGE FORMULA LOGIC |
| Product A | 4/18/2022 | 10 | 40 | FROM Product A WK1 TO WK 13 |
| Product A | 4/25/2022 | 15 | 42.5 | FROM Product A WK2 TO WK 13 |
| Product A | 5/2/2022 | 20 | 45 | FROM Product A WK3 TO WK 13 |
| Product A | 5/9/2022 | 25 | 47.5 | FROM Product A WK4 TO WK 13 |
| Product A | 5/16/2022 | 30 | 50 | FROM Product A WK5 TO WK 13 |
| Product A | 5/23/2022 | 35 | 52.5 | FROM Product A WK6 TO WK 13 |
| Product A | 5/30/2022 | 40 | 55 | FROM Product A WK7 TO WK 13 |
| Product A | 6/6/2022 | 45 | 57.5 | FROM Product A WK8 TO WK 13 |
| Product A | 6/13/2022 | 50 | 60 | FROM Product A WK9 TO WK 13 |
| Product A | 6/20/2022 | 55 | 62.5 | FROM Product A WK10 TO WK 13 |
| Product A | 6/27/2022 | 60 | 65 | FROM Product A WK11 TO WK 13 |
| Product A | 7/4/2022 | 65 | 67.5 | FROM Product A WK12 TO WK 13 |
| Product A | 7/11/2022 | 70 | 70 | FROM Product A WK13 TO WK 13 |
| Product B | 4/18/2022 | 65 | 35 | FROM Product B WK1 TO WK 13 |
| Product B | 4/25/2022 | 60 | 32.5 | FROM Product B WK2 TO WK 13 |
| Product B | 5/2/2022 | 55 | 30 | FROM Product B WK3 TO WK 13 |
| Product B | 5/9/2022 | 50 | 27.5 | FROM Product B WK4 TO WK 13 |
| Product B | 5/16/2022 | 45 | 25 | FROM Product B WK5 TO WK 13 |
| Product B | 5/23/2022 | 40 | 22.5 | FROM Product B WK6 TO WK 13 |
| Product B | 5/30/2022 | 35 | 20 | FROM Product B WK7 TO WK 13 |
| Product B | 6/6/2022 | 30 | 17.5 | FROM Product B WK8 TO WK 13 |
| Product B | 6/13/2022 | 25 | 15 | FROM Product B WK9 TO WK 13 |
| Product B | 6/20/2022 | 20 | 12.5 | FROM Product B WK10 TO WK 13 |
| Product B | 6/27/2022 | 15 | 10 | FROM Product B WK11 TO WK 13 |
| Product B | 7/4/2022 | 10 | 7.5 | FROM Product B WK12 TO WK 13 |
| Product B | 7/11/2022 | 5 | 5 | FROM Product B WK13 TO WK 13 |
hope you can help
regards
Hi,
I can help with solving this question is you can create a Calendar Table and in that table also have a week number column. So please share the download link of your PBI file with your Data Table and the Calendar Table.
