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

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 )
  • C4L84's avatar
    C4L84
    Advocate II
    4 years ago

    The data type of 'FY End'[Date] is date, not text.

     

    The TOTALYTD formula will not accept dd/mm/yy format for the year end arguement, so I need to drop the year from 'FY End'[Date].

     

    I attempted to do this using FORMAT but I think it changes the data type from date to text.

     

    I used your suggested expression but had this:

    "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."

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      C4L84 
      The last argument is the last date of your fiscal year. This is usually a fixed number. But who said you need to drop the year from 'FY End'[Date]? This should be a proper date. Am I missing something