Count status end of each month

[Anonymous]

Hi - hoping someone can help with the DAX needed for a measure to do the following

I have a table of data which has an open date and close date in it. I need to be able to create a line graph which shows the number of cases that were open at the end of each month on a month by month basis. 

Thanks

[v-shex-msft]

Hi @Anonymous ,

You can try to use following measure if it suitable for your requirement:

Measure =
VAR currDate =
    MAX ( Date[Date] )
RETURN
    CALCULATE (
        COUNT ( Table[Column] ),
        FILTER (
            ALLSELECTED ( Table ),
            [OpenDate] <= currDate
                && OR ( [CloseDate] > currDate, [CloseDate] = BLANK () )
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you - however, this isn't quite doing what I need it to.

To calrify - my calendar table has dates spanning 100 years as a reference table. 

I am trying to get the data in the example in my first post to look like this:

Thanks

KH

[Anonymous · Accepted solution]

I have now resolved this by adding a Month Start and Month End Column to my Calendar reference table and then building the following measure:

CountOpenME = 
CALCULATE (
    COUNT( 'Table'[Stud ID] ),
    FILTER (
        GENERATE ( 'Table', 'Calendar' ),
        'Table'[Open Date] <= 'Calendar'[MonthEnd]
            && OR('Table'[Close Date] > 'Calendar'[MonthEnd],'Table'[Close Date]=BLANK())
    )
)