Forum Discussion

ArjanFraaij's avatar
ArjanFraaij
Frequent Visitor
6 years ago

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...