Count of members currently admitted using a dim_date table and a fac_member table

[Anonymous]

I have Member_ID, Admit_Date, and Discharge_Date from a fact table and a CalendarDate from a Dim_Date table.

I am using CalendarDate as the axis on a line graph and want to show the number of members who were currently admitted on each date...meaning the calendar date was between the Admit_Date and Discharge_Date.

This is what I did for the measure, but I don't think it is right.

Currently_Admitted_Measure= CALCULATE(DISTINCTCOUNT(Fac_Member[Member_ID]),FILTER(Dim_Date,Dim_Date[CalendarDate]>MAX(Fac_Member[Admit_Date])),FILTER(Dim_Date,Dim_Date[CalendarDate]<MAX(Fac_Member[Discharge_Date])))

Any help is much appreciated! Thanks!

[v-chuncz-msft]

@Anonymous ,

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/Create-date-Parameter-asat-date-to-get-active-customers/m-p/311306#M137927

[Anonymous]

Hi,

Thanks! This gives me the counts I am looking for! Instead of COUNTROWS, is there something else I can use to also identify the associated Member_ID's as well?

Thanks in Advance,

Ronald