cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors