Forum Discussion
Rolling Average & DatesInPeriod
Hi Anonymous ,
It's pleasant that your problem could be solved, could you please mark my reply as answer? And I suggest you re-create a topic for your following questions that more contributors could help you.
Regards,
Daniel He
Hi Anonymous ,
Based on my test, you could refer to below formula:
PaidSevenDayAvg = var a=MAXX('Labor','Labor'[CalendarDate])
return Calculate(sum(Labor[DailyLabor]),filter(ALL(Labor),Labor[Labor_report_description]="Total Paid (D + I)" &&'Labor'[LocationID]=SELECTEDVALUE(LocationInformation[LocationID]) &&'Labor'[CalendarDate]>a-7&&'Labor'[CalendarDate]<=a))/7
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Daniel,
Yes, this is perfect and is highlighting a couple functions I need to research. Can you please help me with a couple followup questions:
1. Is there a reason you chose to set a variable "a" paired with logical operators compared to the DatesInPeriod function? Is there is limitation in the DateInPeriod function that I do not understand or is my dataset not compatible with that function?
2. The formula you created works as intended, however, is there way to modify it so it aggregates when more than one location is selected in the slicer? For example, if LocationID 120 and 121 are selected, both locations' Labor[DailyLabor] is summed together and then divided by seven. Basically the seven day average for both locations. This would help me make the line chart more functional and also make it easy to look at the company (all locations) as a whole.
Thank you for the continued help. You accomplished in two lines of code something I have been trying to figure out for a week.
Best,
Brett
Hi Anonymous ,
It's pleasant that your problem could be solved, could you please mark my reply as answer? And I suggest you re-create a topic for your following questions that more contributors could help you.
Regards,
Daniel He