Rolling Average & DatesInPeriod

[Anonymous]

Hi,

I have been working to calculate a seven-day rolling average using a nested DatesInPeriod inside the Filter function. I have read several forum posts about this topic (RE: calculating a moving average) and I continue to have difficulty getting a combination of these functions to work. Specifically, when I populate the starting date in the DatesInPeriod function I am returning a table with zero values and I can not figure out why. I am likely missing a basic concept but have yet to identify what this is.

I have created a sample pbix file to help illustrate what I am seeing.  It can be downloaded at this link: https://www.dropbox.com/s/49hkatdoixnl5oe/Sample%20File%201.pbix?dl=0

The file contains three tables:

1.       Calendar (a generic calendar table)

2.       LocationInformation (contains location IDs and other location information)

3.       Labor (contains the data I am trying to calculate)

I have added a measure in the table named PaidSevenDayAvg and this is the formula I am having issues with. The formula is meant to sum the previous seven days of data categorized as “Total Paid ( D + I )” in the Labor[Labor_report_description] column. The data being summed is in the Labor[DailyLabor] column.

Here is an example of the result I want using LocationInformation[LocationID] = 120 as a filter (slicer) and assuming a start date of 2/16/2019 in the DatesInPeriod function.

LocationID   CalendarDate   DailyLabor   Labor_report_description

120                 2/16/2019         70.35           Total Paid (D + I)

120                 2/15/2019         111.22        Total Paid (D + I)

120                 2/14/2019          126.16        Total Paid (D + I)

120                 2/13/2019           118.74       Total Paid (D + I)

120                  2/12/2019           125.69     Total Paid (D + I)

120                  2/11/2019           122.03     Total Paid (D + I)

120                   2/10/2019           55.23       Total Paid (D + I)

The measure should add up the seven numbers under the Daily Labor column (sum = 729.42) and divide by seven, returning a result of 104.20.  This result is meant to be dynamic so every day or every previous day recalculates the rolling average.  

My end goal is to have two measures: one that is able to calculate the rolling average based on a per location basis (filtered using a slicer) and a second that calculates the average of all locations combined (e.g. an aggregation of the whole company, whic is not affected by a slicer using the locations). I would like to put both measures into a line chart so I can compare locations to the whole company. I believe the second measure can be calculated using an ALL(LocationID) in the filter function, but I have yet to get this to work as well 

Thank you for the help and let me know if there is anything else I can provide.  

[v-danhe-msft]

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

[Anonymous]

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 

[v-danhe-msft · Accepted solution]

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