Forum Discussion

C4L84's avatar
C4L84
Advocate II
4 years ago
Solved

TOTALYTD using variable year end date

Hi I am using this formula for running total: No issues with this but I am also using a parameter based on month int: I need the year end date to change in the formula.   I have trie...
TOTALYTD
  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    4 years ago

    Hey C4L84,
    Can you try this? I used your pbix, and it looks like it works. I added a calculated column in your Calendar table:

     

    Calendar Year = YEAR('Calendar'[Date])

     

     

    Then this measure:

    Running Total Spend - Dynamic = 

    VAR _month = FORMAT(MAX('FY end'[Date]), "M")
    VAR _day = FORMAT(MAX('FY end'[Date]), "D")
    VAR _endofyear = DATE(SELECTEDVALUE('Calendar'[Calendar Year]),_month,_day)
    VAR _startofyear = DATE(SELECTEDVALUE('Calendar'[Calendar Year]),_month,_day) +1
    VAR _startdatedynamic = IF( MAX('Calendar'[Date]) < _startofyear, EDATE(_startofyear,-12), _startofyear)
    VAR _enddatedynamic = IF( MAX('Calendar'[Date]) > _endofyear, EDATE(_endofyear,12), _endofyear)
    RETURN

    CALCULATE(
        SUM(Sheet1[Spend]),
        FILTER(
          ALL('Calendar'),
          'Calendar'[Date] >= _startdatedynamic &&
          'Calendar'[Date] <= _enddatedynamic &&
          'Calendar'[Date] <= MAX('Calendar'[Date])
        )
    )


    Compared above measure with the time intelligence DAX function, the yield same results

     

    Let me know how it goes.

Tutu_in_YYC's avatar
Tutu_in_YYC
Super User
4 years ago

Im curious now, can you try this? Since the end date is of type text i.e "31/01". Use the function FORMAT to convert your date to text.

 

Running Total Spend =
VAR _EndDate = FORMAT( MAX('FY end'[Date]), "DD/MM")
RETURN

TOTALYTD( SUM('Invoiced Sales'[Period]), 'Calendar'[Date], _EndDate)

You may need to tweak this according to the date table you have