cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JulietZhu Member
Member

DAX for last N month end day with single month selection

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 )
VAR Edate1=IF(DAY(FDate)=30,Edate+1,IF(DAY(FDate)=28,Edate+3,IF(DAY(FDate)=29,Edate+2,Edate)))
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

 

14.PNG

1 REPLY 1
Community Support Team
Community Support Team

Re: DAX for last N month end day with single month selection

hi, @JulietZhu

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.

For example:

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.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.