DAX to find end dates and count them that are in between Date Dimension

[Karolina411]

I am transferring reports from EXCEL To Power BI and this existing

DAX is not working well in my Power BI and is producing much higher numbers than excel.  The goal is locate and count patients whose Case Management ENDDATE is between each month to determine the # of closed cases. Very frustating.

ClosedCases:=CALCULATE(COUNTROWS('LOS CM'),FILTER('LOS CM',[EndDate]>=STARTOFMONTH(DateDim[DateValue])&& [EndDate]<=ENDOFMONTH(DateDim[DateValue])))

[v-xiaotang · Accepted solution]

Hi @Karolina411 

If you want to get count per month, try the measure below, it returns the count of patients whose Case Management ENDDATE is between each month

ClosedCases :=
CALCULATE (
    COUNTROWS ( 'LOS CM' ),
    FILTER (
        ALL ( 'LOS CM' ),
        MONTH ( 'LOS CM'[EndDate] ) = MONTH ( MIN ( 'LOS CM'[EndDate] ) )
    )
)

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Karolina411 

you have to get red of excel's mentality while working while working with DAX

First set the relationship between the date column of the date table with the EndDate column of your patients table.

2nd build you measure which should be COUNTROWS ( DISTINCT ( 'LOS CM'[Patient ID] ) ) 

If you don't have a patient id column and you 100% sure there are no duplicate records of the same patient then you can use just COUNTROWS  ( 'LOS CM' )

The in the matix visualplace the Year and underneath it the month on the raws. Then place the your measure in the Values

On case you already have a relationship between the two tables, then you can build an in active relationship as described above and then activate it using CALCULATE ( [Mesure], USERELATIONSHIP ( the date column from date table, the end date colum from the patient's table ))