How to calculate 3 months and 6 months average

[bunny18]

Hi, am looking for help to add a 3month and 6 months average to a Defect Detection chart (2020) (pbix attached).

https://drive.google.com/file/d/1idTTxueLZ4PwfZVSeybambEAJ7BNAmTj/view?usp=sharing

I have also put a excel sheet chart which i want to replicate in the pbix.

Could you please help how to achieve it.

[manikumar34 · Accepted solution]

@bunny18 , 

Use something like this on Date column. 

6 Months Average Measure =

CALCULATE(

SUM('DRE created 2020'[DRE2020]),

DATESINPERIOD('DRE created 2020'[TempColumn],LASTDATE('DRE created 2020'[TempColumn]),-6,MONTH)

)

/

CALCULATE(

DISTINCTCOUNT('DRE created 2020'[TempColumn]),

DATESINPERIOD('DRE created 2020'[TempColumn],LASTDATE('DRE created 2020'[TempColumn]),-6,MONTH)

)

Regards,

Manikumar

[amitchandak]

@bunny18 , you can try measure like these with a date table

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

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 :radacad sqlbi My Video Series Appreciate your Kudos.