Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
anaxha
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. 
Thanks in advance for help!
1 ACCEPTED SOLUTION

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

View solution in original post

5 REPLIES 5
reidnmorrison
Resolver II
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])
    )

 

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

anaxha
Frequent Visitor

Thanks for the reply!

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.

Mikelytics
Resident Rockstar
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.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

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

Here is my data model:

anaxha_0-1674200587400.png

Sample data:

anaxha_1-1674200638101.png

Here is my matrix:

anaxha_2-1674200664732.png

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.