Forum Discussion

lilych's avatar
lilych
Icon for Helper II rankHelper II
5 years ago
Solved

Open and closed cases over time

Hello,   I'm trying to create a report that shows the number of cases: Open that month (new and cases that remained open from preceding months) Opened during month Closed during month   SAMP...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, lilych 

    Please check the below picture and the sample pbix file's link down below.

    I suggest having an inactive relationship like below.

    All measures are in the sample pbix file.

     

     

    cases open at start of time periods =
    CALCULATE (
    COUNTROWS ( 'cases' ),
    FILTER (
    'cases',
    'cases'[Created On] <= MAX ( dates[date] )
    && OR ( 'cases'[Closed On] >= MIN ( dates[date] ), 'cases'[Closed On] = BLANK () )
    )
    )
     
    cases open during time periods =
    CALCULATE (
    COUNTROWS ( 'cases' ),
    USERELATIONSHIP ( dates[date], 'cases'[Created On] )
    )
     
    cases closed during time periods =
    CALCULATE (
    COUNTROWS ( 'cases' ),
    NOT ISBLANK ( 'cases'[Closed On] ),
    USERELATIONSHIP ( dates[date], 'cases'[Closed On] )
    )
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM