Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table with hospitals, and procedures, and procedure dates.
I have a measure that counts for hospitals per month as long as they've ever done a procedure - this part is good
M_CountDistActiveAcct = CALCULATE(DISTINCTCOUNT(Procedure[Account__c]), FILTER(ALL(DateTable), DateTable[Date]<=MAX(DateTable[Date])))
Now I also need to know the hospital count previous month and 2 months prior in order to calculate the 3 months average (the requirement is for rolling 3 months trending chart.
My Previous month hospital count is like below:
M_CountDistActiveAcctPreMonth = CALCULATE(DISTINCTCOUNT(Procedure[Account__c]), FILTER(ALL(DateTable), DateTable[Date]<=DATEADD(ENDOFMONTH(DateTable[Date]), -1, MONTH)))
but this doesnt returns a value back - I also found endofmonth doesnt not always return the right dates, but I have to use endofmonth or lastdate to return a one row one column table for "dateadd" to work. Max returns a date value which doesnt work in "dateadd". I have calendar table behind linked to precedure dates.
Thank you so much!
NM
Solved! Go to Solution.
@nanma94 : Thanks for the clarification, please modify the measure. I am assuming if the procedure has not been done, the cell will be blank.
=CALCULATE(DISTINCTCOUNT(Hospitals[Hospitals]),DATESBETWEEN(Cal[Date],EDATE(MAX(Cal[Date]),-3),MAX(Cal[Date])),FILTER(ALL(Hospitals),Hospitals[Procedure]<>BLANK()))
@nanma94 Can you please share the file?
Is your final output 3 months rolling avg ?
@ChandeepChhabra my last column on the screen shot is only the hospital count of previous month.
I would need to know the count of current month, previous month, and 2 months prior to calculate rolling 3 months average hospital count. In real life, the month over month hospital count changes are like 120, 140, 150. So the incremental is only the new hospitals.
I havent figure out a good way to mask my data:(
@nanma94 I assumed some dummy data and created a rolling distinct count of hospitals for the last 3 months
Last 3 months Rolling Count =CALCULATE(DISTINCTCOUNT(Hospitals[Hospitals]),DATESBETWEEN(Cal[Date],EDATE(MAX(Cal[Date]),-3),MAX(Cal[Date])))
You can download the file here
@ChandeepChhabra Thank you for looking into this. I understand the rolling 3 months revenue can be done in the way you suggested. But for # of active hospitals (definition is whenever they have a procedure done in the entire procedure table history, from that time point on, they start to be counted as an active hospital), I think I have to do it in the tedious way - get current month, prior month, and 2 months prior. sum them up and divided by 3. Because if you give a time range (3 months) to look at the # of active hospitals, its the same as by end of month 3 result. Its not a 3 month rolling average.
@nanma94 : Thanks for the clarification, please modify the measure. I am assuming if the procedure has not been done, the cell will be blank.
=CALCULATE(DISTINCTCOUNT(Hospitals[Hospitals]),DATESBETWEEN(Cal[Date],EDATE(MAX(Cal[Date]),-3),MAX(Cal[Date])),FILTER(ALL(Hospitals),Hospitals[Procedure]<>BLANK()))
@ChandeepChhabra Thank you so much for the reply. I got ideas from your EDATE(MAX(DateTable[Date]),-3)
I leveraged this to get my prior month, and 2 months prior.
M_CountDistActiveAcctPreMonth = CALCULATE(DISTINCTCOUNT(Procedure[Account__c]), FILTER(ALL(DateTable), DateTable[Date]<=EDATE(MAX(DateTable[Date]),-1)))
M_CountDistActiveAcct2MonthPrior = CALCULATE(DISTINCTCOUNT(Procedure[Account__c]), FILTER(ALL(DateTable), DateTable[Date]<=EDATE(MAX(DateTable[Date]),-2)))
and my table looks correct now -
Coming here asking beats hours of googling and guess work:) thanks a lot,
NM
@nanma94 @ChandeepChhabra This is not working, can you help me.
I have the below measure for disinct count of previous month. the value is blank