Forum Discussion

C4L84's avatar
C4L84
Advocate II
4 years ago
Solved

TOTALYTD variable year

Hi   I need to pass thorugh a variable year end to TOTALYTD, however it's not working as hoped:      Running Total Spend = VAR _EndDate = FORMAT(MAX('FY end'[Date]), "DD/MM") RETUR...
TOTALYTD
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi C4L84 
    Here is the file with the solution https://we.tl/t-LZzcXfkSG3
    Your Mesure Code is

    Running Total Spend = 
VAR _EndDate =
    MAX ( 'FY end'[Date] )
VAR _CurrentYear =
    SELECTEDVALUE ( 'Calendar'[Financial year] )
VAR _StartDate = 
    DATE ( _CurrentYear, MONTH ( _EndDate ), DAY ( _EndDate ) )
VAR _CurrentDate =
    MAX ( 'Calendar'[Date] )
VAR _Result =
    CALCULATE (
        SUM ( Sheet1[Spend] ),
        'Calendar'[Date] >= _StartDate,
        'Calendar'[Date] <= _CurrentDate
    )
RETURN
    _Result

C4L84's avatar
C4L84
Advocate II
4 years ago

Hi tamerj1 - it's easier to understand

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Then can you try without the time format?

    • C4L84's avatar
      C4L84
      Advocate II
      4 years ago

      I'm sorry, I don't understand... I'm not formatting the time - it's formatted dd/mm day/month eg 22/03

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        C4L84 
        Ok. Then I guess you mean to say that the date has text data type and this is the source of the error. Usually you should check and fix data type of all columns in power query before loading the data. However, if this is not what you want to do and if you don't want to create a new column in the 'FY End' table with the correct data type date then you can use the following code. Hopefully, DAX will automatically convert the string numbers into integers:

        Running Total Spend =
VAR _EndDate =
    MAXX (
        ADDCOLUMNS (
            'FY end',
            "@Date", DATE ( 1, RIGHT ( 'FY end'[Date], 2 ), LEFT ( 'FY end'[Date], 2 ) )
        ),
        [@Date]
    )
RETURN
    TOTALYTD ( SUM ( 'Invoiced Sales'[Spend] ), 'Calendar'[Date], _EndDate )

        If the error remains then use this one

        Running Total Spend =
VAR _EndDate =
    MAXX (
        ADDCOLUMNS (
            'FY end',
            "@Date",
                DATE ( 1, INT ( RIGHT ( 'FY end'[Date], 2 ) ), INT ( LEFT ( 'FY end'[Date], 2 ) ) )
        ),
        [@Date]
    )
RETURN
    TOTALYTD ( SUM ( 'Invoiced Sales'[Spend] ), 'Calendar'[Date], _EndDate )