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.

Frequent Visitor

## Cumulative with useraltionship

Hi!

I am trying to calculate cumulative value of closed cases.

My matrix table uses calendar dates as columns and I am using inactive relationship with [closed_date]. The measure I am using to calculate cases closed (not cumulative) is :

Case closed = CALCULATE(COUNTROWS('fact_AllCases'), USERELATIONSHIP('Calendar'[Date],'fact_AllCases'[Closed Date]))
Every measure that I created doesn't work properly.
1 ACCEPTED SOLUTION
Resolver II

Or if it is stretching over multiple years this should w

``````Running Closed Total =
CALCULATE (
COUNTROWS('fact_AllCases') ,
'Calendar'[Date] <= MAX ('Calendar'[Date]),
'fact_AllCases'[Status] = "Closed",
USERELATIONSHIP('Calendar'[Date], 'fact_AllCases'[Closed Date])
)``````

ork

5 REPLIES 5
Resolver II

Does this give you the desired result?

Open

``````Open =
CALCULATE (
TOTALYTD(
COUNTROWS('fact_AllCases'),
'Calendar'[Date],
'fact_AllCases'[Status] = "Open"
),
USERELATIONSHIP('Calendar'[Date], 'fact_AllCases'[Closed Date])
)``````

and for closed

``````Closed =
CALCULATE (
TOTALYTD(
COUNTROWS('fact_AllCases'),
'Calendar'[Date],
'fact_AllCases'[Status] = "Closed"
),
USERELATIONSHIP('Calendar'[Date], 'fact_AllCases'[Closed Date])
)``````

Resolver II

Or if it is stretching over multiple years this should w

``````Running Closed Total =
CALCULATE (
COUNTROWS('fact_AllCases') ,
'Calendar'[Date] <= MAX ('Calendar'[Date]),
'fact_AllCases'[Status] = "Closed",
USERELATIONSHIP('Calendar'[Date], 'fact_AllCases'[Closed Date])
)``````

ork

Frequent Visitor

Yes this measure works for me, but it's not cumulative.

I want to do the same measure but cumulative (the dates in visuals come from calendar which has inactive relationship with [Closed date] field that I am using)- and it has to stay that way.

Resident Rockstar

Hi  @anaxha

Looks like a proper measure in the first place depending on what you want to achive and what the issues are you are facing. Can you please share

- sample data with

- picture of data model

- desciption of what you want to achieve

- show on the example the issues you face?

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Frequent Visitor

Here is my data model:

Sample data:

Here is my matrix:

So what I am trying to do is get this numbers to be cumulative by month.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors