cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## cumulative totals of measure

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:

1 ACCEPTED SOLUTION
Microsoft Employee

Hi @Anonymous,

```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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft Employee

Hi @Anonymous,

```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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.