Forum Discussion

Caz_16's avatar
Caz_16
Icon for Helper II rankHelper II
5 years ago
Solved

Cumulative Calculation to Date

Hi all, 

 

So I found this great formula on the forum for calculating a measure for FYTD, and then having the lines stop and not continue on until the end of the calendar year. However, there is one issue, the calculation is being done too well, I have different categories in which I am summing TIME[Duration] to date, and some are much less used that others. See my chart below, you can see that the light purple is not continuing to the end and it is because the last entry date for that particular category was a few weeks ago. What can I change in the measure to get the line to continue to where it is caught up with the rest of the categories. 

 

 

Cumulative Hours by Day = 

VAR LastDate1 = CALCULATE(LASTDATE('TIME'[EntryDate]),ALL('TIME'))

RETURN
IF(
    SELECTEDVALUE('Calendar Table'[Date])> LastDate1,
    BLANK(),
    CALCULATE(
        SUM(
            'TIME'[Duration]
        ),
        FILTER(ALLSELECTED('Calendar Table'[Date]),
        'Calendar Table'[Date] <= MAX('Calendar Table'[Date])
        )
    )
)

 

 

DAX
Need Help
  • Caz_16's avatar
    Caz_16
    5 years ago

    Anonymous ,

     

    I can see what you were doing, working with only variables, but unfortunately It produced the same thing that my previous formula did. However, you did set me on track for what I was looking for. I had to replace the ALL(TIME[Entry Date]) with ALLCROSSFILTERED(TIME[Entry Date]), because the ALL was still enforcing the filtering coming from the outside table. ALLCROSSFILTERED got rid of those filters coming from the table where I keep my Time Entry Categories. So my final formula wound up being:

     

    Cumulative Cost by Day = 

VAR LastDate1 = CALCULATE(LASTDATE('TIME'[EntryDate]),ALLCROSSFILTERED('TIME'))

RETURN
IF(
    SELECTEDVALUE('Calendar Table'[Date])> LastDate1,
    BLANK(),
    CALCULATE(
        SUM('TIME'[COST]),
    FILTER(ALLSELECTED('Calendar Table'[Date]),
        'Calendar Table'[Date] <= MAX('Calendar Table'[Date])
        )
    )
)

     

     

    Thank you for your time and reply. 

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Caz_16 .Have you tried datesytd?

    example

    YTD = CALCULATE(SUM('Time'[Duration]),DATESYTD('Date'[Date],"12/31")) // Change end date based on your FY

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    What about this?

    [Cumulative Hours by Day] = 
// this returns the very last
// date that exists in the
// fact table TIME regardless
// of any selections made
VAR __lastDateWithData =
    CALCULATE(
        MAX('TIME'[EntryDate]),
        ALL('TIME')
    )
var __currentDate =
    SELECTEDVALUE(
        'Calendar Table'[Date],
        // +1 in order for this to
        // work in the IF condition
        __lastDateWithData + 1
    )
var __result =
IF(
    and(
        HASONEFILTER( 'Calendar Table'[Date] )
        __currentDate <= __lastDateWithData
    ),
    
    CALCULATE(
        SUM( 'TIME'[Duration] ),
        'Calendar Table'[Date] <= __currentDate,
        ALLSELECTED('Calendar Table')
    )
)
return
    __result
    • Caz_16's avatar
      Caz_16
      Icon for Helper II rankHelper II
      5 years ago

      Anonymous ,

       

      I can see what you were doing, working with only variables, but unfortunately It produced the same thing that my previous formula did. However, you did set me on track for what I was looking for. I had to replace the ALL(TIME[Entry Date]) with ALLCROSSFILTERED(TIME[Entry Date]), because the ALL was still enforcing the filtering coming from the outside table. ALLCROSSFILTERED got rid of those filters coming from the table where I keep my Time Entry Categories. So my final formula wound up being:

       

      Cumulative Cost by Day = 

VAR LastDate1 = CALCULATE(LASTDATE('TIME'[EntryDate]),ALLCROSSFILTERED('TIME'))

RETURN
IF(
    SELECTEDVALUE('Calendar Table'[Date])> LastDate1,
    BLANK(),
    CALCULATE(
        SUM('TIME'[COST]),
    FILTER(ALLSELECTED('Calendar Table'[Date]),
        'Calendar Table'[Date] <= MAX('Calendar Table'[Date])
        )
    )
)

       

       

      Thank you for your time and reply. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago
        If removing all filters with ALL does not work, it means somewhere in your code you must have a bidirectional filter(s) enabled. Is this correct? If not, would you please care to explain why ALL did not work whereas ALLCROSSFILTERED did? Thanks.