Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |