Dax measure to calculate average increase

[GreggPowell]

Want to create a DAX measure that averages the most recent last seven (one week's worth) of counts in a count column; averages seven (one week's worth) fields in a count column 60 days ago, and then determines the percent increase in the 7 day recent average count over the 7 day average in the count field from 60 days ago.

Can use either the index column or the date column I guess in the equation to track the most recent 7 days, and the 7 days- 60 days ago.
Any help would be much appreciated.
Thanks.
Gregg 
Sierra Vista, AZ

[grantsamborn · Accepted solution]

Hi

Let me know if something like this helps.

https://1drv.ms/u/s!AnF6rI36HAVkhPFSPkYVQaRQxxTZDQ?e=HE9Hkb

[GreggPowell · Accepted solution]

I was able to solve this by creating 5 different measure each measure depends on the preceeding measure - so they must be created in succession...

SumAmount = SUM(PS_PipeDriveDeals_Running_Totals[Current Clients])

7DayMovingSum = CALCULATE([SumAmount],DATESINPERIOD(PS_PipeDriveDeals_Running_Totals[Date],LASTDATE(PS_PipeDriveDeals_Running_Totals[Date]),-7,DAY))

Last7DayMovingAverage = PS_PipeDriveDeals_Running_Totals[7DayMovingSum] / CALCULATE(DISTINCTCOUNT(PS_PipeDriveDeals_Running_Totals[Date]),DATESINPERIOD(PS_PipeDriveDeals_Running_Totals[Date],LASTDATE(PS_PipeDriveDeals_Running_Totals[Date]),-7,DAY))

7DayMovingAverage_60DaysAgo = PS_PipeDriveDeals_Running_Totals[7DayMovingSum_60DaysAgo] / CALCULATE(DISTINCTCOUNT(PS_PipeDriveDeals_Running_Totals[Date]),DATESINPERIOD(PS_PipeDriveDeals_Running_Totals[Date],LASTDATE(PS_PipeDriveDeals_Running_Totals[Date])-60,-7,DAY))

PercentIncrease_7DayAveragePercentIncreaseOver7DayAverage60DaysAgo = (1-(PS_PipeDriveDeals_Running_Totals[7DayMovingAverage_60DaysAgo]/PS_PipeDriveDeals_Running_Totals[Last7DayMovingAverage]))*100

[danextian]

Hi @GreggPowell 

Please post a link to a link an excel file contaning your sample data instead of an image (a link to One/GDrive, Dropbox, etc).  And in that excel file, include formulas of the measure you want (how would you do those averages in Excel) to be translated to DAX. Also, please clarify whether you really want a measure (as in the title of your post) or columns (as  in the description). Measures in DAX can only be viewed using visuals. Calculated columns on the other hand are stored in the model as a column - they can be used as a dimension field in visuals and are viewable in the Data view.

[GreggPowell]

My apologies. I was not at all clear in my initial query. I am using Power BI, not excel. The actual data sits in a csv in sharepoint and contains a great deal more information that is corporate sensitve so I can't post a link to it. I only posted the excel image to kind of provide an exemplar.  I just want to create a measure that averages the last 7 days worth of count data (call this A); then averages 7 days worth of count data from 60 days ago (call this B); and then figures the % increase (1-A/B). And again  -  this is in Power BI.  The data in the csv is updated by an API daily - so a new row is added for the new day wil all new counts. The Power BI report is published in Power BI service and updates each day also - so this would produce a running report of the average increase.

Thanks for any help.

[grantsamborn · Accepted solution]

Hi

Let me know if something like this helps.

https://1drv.ms/u/s!AnF6rI36HAVkhPFSPkYVQaRQxxTZDQ?e=HE9Hkb