Forum Discussion

lilych's avatar
lilych
Helper 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
Super 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

 

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