I need last 13 month trending data with one month slicer (not date range). Also for each month, I only need capture the last day's data and not sum the whole month. For example, if Aug 2018 is chosen, I need show data for 08/31/2018, 07/31/2018, 06/30/2018, 05/31/2018 etc.
The DAX I am using is
TaskOutstanding_RCM(last 13 months) =
VAR MaxFactDate =
CALCULATE ( MAX ( Task[CreatedDate]), ALL ( 'Date') )
VAR FDate =
ENDOFMONTH ( 'Date'[Date] )
VAR Edate =
EDATE ( FDate, -13 )
RETURN IF ( MaxFactDate <= MAX ( 'Date'[Date] ) && MaxFactDate > Edate1,
CALCULATE ( Task[TaskOutstanding] , ALL('Date'[EOM])))
But the result is not correct. Please help the above DAX. Thanks
The dummy data link: https://1drv.ms/u/s!AlYpYKwSuOKxhFq5jYCx180jIA1A
Your measure with data model is a little bit complicated, This relates to the row context and filter context, you filter Date (bins), but drag createddate (bins) into table visual.
if Aug 2018 is chosen, TaskCompleted should be 61587 in Aug 2018, but it is divided into the different createddate (bins).
So if you need to use Date (bins) in visual and measure instead of createddate (bins).
hope it can help you.
I will have a try on it too.