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

RETURN

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

 

It seems as though the FORMAT function is changing the data type to text and therefore it isn't recognised by the TOTALYTD because it interperets the text as arguement 3 - ie the filter part.

 

Is there a way to pass a variable date through to the year end arguement of the TOTALYTD formula?

 

Many thanks

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

25 Replies

    • 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

    Hi C4L84 

    why do you need to format the date before calculating the YTD?

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    C4L84 , I doubt you can use dynamic value for that. It can take date in dd/mm too

     

    Try like

     

    Running Total Spend =

    VAR _EndDate = MAX('FY end'[Date])
    VAR _StartDate = eomonth(_EndDate,-12)+1 //or// date(year(_EndDate)-1, month(_EndDate) , day(_EndDate)+1)

    RETURN

    calculate(SUM('Invoiced Sales'[Spend]), filter('Calendar','Calendar'[Date] >=_StartDate && 'Calendar'[Date] <= _EndDate ))

    or

     

    Running Total Spend =

    VAR _EndDate = MAX('FY end'[Date])
    VAR _StartDate = eomonth(_EndDate,-12)+1 //or// date(year(_EndDate)-1, month(_EndDate) , day(_EndDate)+1)

    RETURN

    calculate(SUM('Invoiced Sales'[Spend]), filter(all('Calendar'),'Calendar'[Date] >=_StartDate && 'Calendar'[Date] <= _EndDate ))

    • C4L84's avatar
      C4L84
      Advocate II
      4 years ago

      Thank you for your suggestions amitchandak, however neither of them work:

      1. 

      2.

      I've uploaded a test pbix to dropbox here