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 

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.

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

    Hi, lilych 

    Please check the below.

     

    https://www.dropbox.com/s/igbo06o9oh7xc5z/Test%20Cases.pbix?dl=0 

     

    The reason is that the column was not date-type. It was dateandtime-type, and this cannot be connected to dim-date table. I added time-column, and changed the initial column's data type to date type.

     

    Thanks.

     

    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