Forum Discussion
ArjanFraaij
6 years agoFrequent Visitor
Cumulative Total within a date range
Can some explain this to me or at least tell me what I do forget?
Report needs to display cumulative total based on ISO Week numbers.
ISO Week 2020-01 runs from 30-12-2019 - 05-01-2020, using below forumula works great, only because YTD for week 01 30&31-12-2019 are not calculated, YTD starts from 01-01-2020 ofcourse..
Uren budget cumulatief YTD =
VAR MaxDate = MAX ( LookUp_DeclaratieDatum[Date] )
VAR MinDate = MIN(LookUp_DeclaratieDatum[Date])
RETURN
CALCULATE (
IF(ISBLANK([Totaal uren budget]);0;[Totaal uren budget]) ;
DATESYTD(LookUp_DeclaratieDatum[Date]);
LookUp_DeclaratieDatum[Date] <= MaxDate;
ALL ( LookUp_DeclaratieDatum )
)
Made me thing to change YTD in DATESBETWEEN... only that doesn't work right as you can see in the matrix below.
Uren budget cumulatief Range =
VAR MaxDate = MAX(LookUp_DeclaratieDatum[Date])
VAR MinDate = MIN(LookUp_DeclaratieDatum[Date])
VAR NumberOfDays = DATEDIFF(MinDate;MaxDate;DAY)
RETURN
CALCULATE (
IF(ISBLANK([Totaal uren budget]);0;[Totaal uren budget]) ;
DATESBETWEEN(LookUp_DeclaratieDatum[Date];MinDate;MaxDate);
ALL(LookUp_DeclaratieDatum)
)
Result
What am I forgetting? or is it just not posible to display a cumulative total in this way based on a date range...
1 Reply
- AnonymousNot applicableFor custom calendars, like ISO, you CANNOT use the time-intel functions as they only work against the standard calendar.
https://www.sqlbi.com/blog/marco/2013/02/19/dax-time-intelligence-for-4-4-5-calendar-iso-calendar-and-other-custom-ones-dax-powerpivot/
Best
D