customers count of previous month

[nanma94]

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

[ChandeepChhabra]

@nanma94 Can you please share the file?

Is your final output 3 months rolling avg ?

[nanma94]

@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:(

[ChandeepChhabra]

@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

[nanma94]

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

[ChandeepChhabra · Accepted 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]

@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

[Anonymous]

@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 

HC KTN Employee previous month = CALCULATE(DISTINCTCOUNT('Data'[Column]),FILTER('Data','Data'[Temp/KTN]="KTN"),FILTER('Data','Data'[EOMonth]<=EDATE(MAX(Data[EOMonth]),-1)))

[ChandeepChhabra]

@nanma94 It could also be done using DATEADD nevertheless I am glad it worked!