cancel
Showing results for
Did you mean:
Frequent Visitor

## Issue with Time Intelligence Functions

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

Kind regards

Nick Singh

3 REPLIES 3
Super User IV

@Nick_Singh 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-TIT...

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Super User IV

@Nick_Singh , 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))

Proud to be a Super User!

Frequent Visitor

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors