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

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
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

 

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

    Thanks Jihwan_Kim - this working great so far.

     

    I am running into an issue with the cases closed during the time period where it is not allocating it correctly (see snapshot below). I double checked the relationship on the date table to the case table and all looks correct. It is inactive. Do you know why this may be happening?

     

     

     

    Also, my date table has a wide range of dates (1900-2100). I think this may be affecting the range of dates in the matrix. Is there a way I can limit the dates shown so that it is only showing up to the current month?

     

    Thank you -

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      5 years ago

      Hi, lilych 

      Thank you for your feedback.

      I am not sure, but please check your measure contains the below condition.

       

      NOT ISBLANK ( 'cases'[Closed On] ),

       

      Or, please share your sample pbix file's link here, then I can try to look into it to find out the cause.

      Thanks.

  • RUSSGOVE's avatar
    RUSSGOVE
    New Member
    4 years ago
    ok, i'm confused! 
    in the bellow expresression
     
    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 () )
    )
     
    shouldn't 
    'cases'[Created On] <= MAX ( dates[date] )
    be 
    'cases'[Created On] <= MIN ( dates[date] )
    I mean if it was open at the start of the period it must have men opened some tome before the period began