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.

      • C4L84's avatar
        C4L84
        Advocate II
        4 years ago

        Hi Tutu_in_YYC this is excellent! Thank you very much for your assistance - it's much appreciated! Well done