Issue with Time Intelligence Functions

[Anonymous]

Hi Guys

I am trying to work on this data set where I need to calculate 3 month rolling average of amount invoiced by a doctor (referred to as Doctor 1 below). Each doctor provides multiple services each day and hence every date is repeated multiple times (ranging 2-6).

So when I calculate 3 mth rolling average, it divides the average amount by total number of dates (rather than distinct dates). Would really appreciate your help. My DAX is: 

Rolling Avg 3 Months =

CALCULATE(AVERAGE(Rad_Reporting[Invoice Total]), DATESINPERIOD(Dim_Date[Date], LASTDATE(Dim_Date[Date]), -3, MONTH))

The dates in 'Datesinperiod' come from date table.

Source Data:

Radiologist	Date	Service	Invoice Total	Report Count
Doctor 1	1/07/2019	Consultations	75	1
Doctor 1	1/07/2019	CT	8,299	16
Doctor 1	1/07/2019	MRI	6,598	17
Doctor 1	1/07/2019	Screening	990	2
Doctor 1	1/07/2019	Ultrasound	3,344	15
Doctor 1	1/07/2019	X-Ray	474	8
Doctor 1	2/07/2019	CT	3,424	8
Doctor 1	2/07/2019	MRI	6,023	14
Doctor 1	2/07/2019	Screening	250	1
Doctor 1	2/07/2019	Ultrasound	2,339	7
Doctor 1	2/07/2019	X-Ray	316	4
Doctor 1	3/07/2019	CT	2,882	7
Doctor 1	3/07/2019	MRI	10,446	24
Doctor 1	3/07/2019	Screening	495	1
Doctor 1	3/07/2019	Ultrasound	1,956	6
Doctor 1	3/07/2019	X-Ray	238	3
Doctor 1	7/07/2019	CT	451	1
Doctor 1	7/07/2019	MRI	13,224	33

Thanks in advance.

Kind regards

Nick Singh

[amitchandak]

@Anonymous , Not very sure. If you need monthly avg divide by month or by distinct date

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

Rolling dates = CALCULATE(Distinctcount(Sales[Sales date]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling dates = CALCULATE(Distinctcount(Sales[Sales date]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-3,MONTH))

as distinct count do take calculation then try with the month

Sales Month = eomonth([Sales date],0)

Rolling dates = CALCULATE(Distinctcount(Sales[Sales date]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling dates = CALCULATE(Distinctcount(Sales[Sales date]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-3,MONTH))

[Anonymous]

Thanks @amitchanda, your formulas helped shape my thinking in getting the desired result.

[Greg_Deckler]

@Anonymous You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008