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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 419 members 4,231 guests
Please welcome our newest community members: