Cumulative Pending Cases

[Anonymous]

Hi everyone, 

I've been trying to write a measue for cumulative pending cases where it should interact with date filter accordingly as point in time.

I have data where i have a startdate, enddate for each status of case and case submitted date.

Case submitted date is active relation with a date colum in claender table and  an inactive relation between status start date and date.

In my scenario, there are few different case status (P,SI,FP) which all together known as Pending.

Data looks as above.

I am using the following DAX, but its not generating the required results.

Cumulative Measure = CALCULATE ( DISTINCTCOUNT( Data[GUID] ), FILTER('Data', AND('Data'[ENDDATE]<= MAX('Date'[Date]), Data[STARTDATE]>= MIN('Date'[Date])) ))

Expected result is as follows where there are total 4 pending cases as of today in October, 7 pending cases at the end of Sep , 3 pending cases at the end of Aug and so on.

If we selected the date filter from 04/01/2018 to 07/10/2018. On 7th month (July) it should show the number of applications that are pending on 07/10/2018. 

Thanks in advance.

[Anonymous]

You can try, this should count the project that where pending during the month:

VAR miDate = MIN('Date'[Date]) // Start of the month in your graph 
VAR mxDate = MAX('Date'[Date])  // End of the month in your graph


RETURN
CALCULATE ( 
    DISTINCTCOUNT( Data[GUID] ), 
    FILTER(
       ALL('Data'), 
       // Projects that stared within the month
       (miDate <= 'Data'[STARTDATE] &&  'Data'[STARTDATE] <= mxDate)
       ||
       // Projects that ended within the month
        (miDate <= 'Data'[ENDDATE] &&  'Data'[ENDDATE] <= mxDate)
       ||
       // Projects that are pending the whole month
        ('Data'[STARTDATE] <= miDate  &&  mxDate <= 'Data'[ENDDATE])
       ||
       // Projects that are pending the during month
        (miDate <= 'Data'[STARTDATE]  &&  'Data'[ENDDATE] <= mxDate)
    ),
    'Data'[STATUS] IN {"P","V","F"},     // Filters on pending
    ALL('Date')  // Removes the date filter (not sure if you need to this)
)

And if you would like to get those that are pending on the end of the month:

VAR mxDate = MAX('Date'[Date])  // End of the month in your graph


RETURN
CALCULATE ( 
    DISTINCTCOUNT( Data[GUID] ), 
    FILTER(
       ALL('Data'), 
        ('Data'[STARTDATE] <= mxDate  && mxDate <= 'Data'[ENDDATE] ) 
    ),
    'Data'[STATUS] IN {"P","V","F"},     // Filters on pending
    ALL('Date')  // Removes the date filter (not sure if you need to this)
)

If it does not work I hope that it directs you to the right direction 🙂

Regards,

Kristjan

[Anonymous]

Hi Kristjan,

Thanks for your reply.

I am using end of month dax but i am getting some redendancy for latest month

The dax is not working with some other filters that I need to represent on dashboard.

I tried few scenarios but no luck.