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.

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

Hi C4L84 ,

 

You missed a argument befor "year_end_date", [filter].

TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

 

So if you have a calendar table, please try this 

Measure = TOTALYTD(SUM('Table'[Values]),'calendar'[Date],ALL('calendar'),"6/30")
if no calendar table,
Measure 2= TOTALYTD(SUM('Table'[Values]),'Table'[Date],all('Table'),"6/30") 
result: 
 

Pbix in the end you can refer.

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mbers find it more quickly.

  • C4L84's avatar
    C4L84
    Advocate II
    4 years ago

    I've included the ALL in the filter arguement but I am now getting this error:

    "The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

     

    I have a pbix that I could share but I cannot find a way to upload...?

    • C4L84's avatar
      C4L84
      Advocate II
      4 years ago

      I've uploaded the file to dropbox, here's the link

      • Tutu_in_YYC's avatar
        Tutu_in_YYC
        Super User
        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.