Forum Discussion
Anonymous
6 years agoNot applicable
Cumulative Pending Cases
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, enddat...
Anonymous
6 years agoNot applicable
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
6 years agoNot applicable
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.