summary table on rolling average based on filters on column

[Anonymous]

Gurus,

New to Power BI thingy and would appreciate if I can get any help on this (I tried to use quick measure with rolling averages but getting error on Dates. My problem is how can I get a 7 day rolling average on data set which runs in 40k rows. Below i will try to explain... 

how to create a summary table showing rolling averages based on dates 

Data Sheet:

Column A having dates (multiple rows have same date) 

Column B have values

Column C have values

Column D should be a filter (Completed / Pending)

Column E Should be a Filter (Phases)

Column F should be Sum(columnB values)/ average of column C based on a single date

Dates	Distance	Labor Hrs	Status	Phase	Average
2/1/2020	100	50	Completed	01	6
2/1/2020	100	50	Completed	01	6
2/1/2020	100	50	Completed	01	6
	100		Pending	01
2/2/2020	100	59	Completed	01	--
2/2/2020	100	59	Completed	01	--

and so on.........

Summary Data:

Column A - Unique Dates from Data Sheet

Column B - 7 day Rolling average of Column D (from Data Sheet) with respect to column A unique dates 

Column A	Column B
2/1/2020	1.75
2/2/2020	so on...

Also, while calculating the rolling average first 7 days should be blanked before the rolling average can start computing.

Any help will be much appreciated.

Thanks in anticipation

[amitchandak]

Make sure you have date table joined to your date in the table

and you can formula like

Rolling 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-7,DAY))
Rolling 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-7,DAY))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

@amitchandak Thanks for Reply Amit, however there is not denominator to do the average in your formula, secondly there are two formulas (Is that Either/ or)? Also, i confirmed that my date is generated from DAX and its assigned Date under modeling tab. Still I am getting time-intelligence function error. Not Sure where I am doing wrong. Also, which expression should I use in dinominator as the result I am getting is an addition of 7 days