Column showing 3 and 12 months average

[Heursqem]

Hi team

I am trying to create an overview like this one:

My source table loooks simular to this one:

I have tried many setups, but have not been able to come close at all.

Anyone who can give some guidance how to set this up.

Appreciated big time

R

[Heursqem]

Thanks Amitchandak; I tweaked it a bit and now it works fine

At the same time, I continue to learn....

Thanks

Heursqem

[amitchandak · Accepted solution]

@Heursqem ,

create a date from month and year

date = date([year],[month],1)

Join it with date table

previous 12 Avg license= CALCULATE(averagex(filter(Table, Table[Type] = "License"), Table[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

previous 3 Avg license= CALCULATE(averagex(filter(Table, Table[Type] = "License"), Table[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

previous 12 Avg Services= CALCULATE(averagex(filter(Table, Table[Type] = "Services"), Table[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

previous 3 Avg Services= CALCULATE(averagex(filter(Table, Table[Type] = "Services"), Table[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,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.