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

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