cumulative totals of measure

[Anonymous]

How to calculate the cumulative totals of measure in following example:

 The model contains a date table and fact table called Internal.The measure EOMClosed is obtained by using

EOMClosed = CALCULATE(distinctcount(Internal[ID]),userelationship(Internal[CloseDate],'Date'[Date]))

The measure when sliced by Date dim gives me number of tickets that got closed each month.I want to make it like cumulative/running.

 EOMOpen is number of open tickets each month sliced by date like :

EOMOpen = CALCULATE(DISTINCTCOUNT([ID]),FILTER(ALL(Internal),(Internal[CloseDate]=blank()||Internal[CloseDate]>EOMONTH([OpenDate],0))&&Internal[FirstDate]<=max('Date'[Date])))

I tried to do the same with EOMClosed but turns out the userelationship is not propogating the context to filter function :

EOMClosed = CALCULATE(distinctcount(Internal[ID]),userelationship(Internal[CloseDate],'Date'[Date]),filter(Internal,[CloseDate]<=max(Date[Date]))

Pls guide me to fix the EOMClosed to get cumulative values.

Here is a sample file:

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Try this formula, please.

EOMClosed =
CALCULATE (
    DISTINCTCOUNT ( Internal[ID] ),
    USERELATIONSHIP ( Internal[CloseDate], 'Date'[Date] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= EOMONTH ( MAX ( 'Internal'[OpenDate] ), 0 )
    ),
    ISBLANK ( Internal[CloseDate] ) = FALSE ()
)

Best Regards,

Dale