Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
@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))
Thanks @amitchanda, your formulas helped shape my thinking in getting the desired result.
@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
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |