cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular 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:

RadiologistDate Service Invoice Total Report Count
 Doctor 11/07/2019 Consultations751
 Doctor 11/07/2019 CT8,29916
 Doctor 11/07/2019 MRI6,59817
 Doctor 11/07/2019 Screening9902
 Doctor 11/07/2019 Ultrasound3,34415
 Doctor 11/07/2019 X-Ray4748
 Doctor 12/07/2019 CT3,4248
 Doctor 12/07/2019 MRI6,02314
 Doctor 12/07/2019 Screening2501
 Doctor 12/07/2019 Ultrasound2,3397
 Doctor 12/07/2019 X-Ray3164
 Doctor 13/07/2019 CT2,8827
 Doctor 13/07/2019 MRI10,44624
 Doctor 13/07/2019 Screening4951
 Doctor 13/07/2019 Ultrasound1,9566
 Doctor 13/07/2019 X-Ray2383
 Doctor 17/07/2019 CT4511
 Doctor 17/07/2019 MRI13,22433

 

Thanks in advance.

 

Kind regards

Nick Singh

3 REPLIES 3
Highlighted
Super User IV
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...


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
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))

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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