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

Unfortunately this has not worked:

 

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Ok I have to admit that this the advantage of time intelligence functions 😜

However, I will try to think of something. Will get back to if I find a solution. 

  • tamerj1's avatar
    tamerj1
    Community Champion
    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've amended the pbix to include the calendar year and made the change - it is now working! Thank you so much for your help, couldn't have done it without you. Well done!

  • C4L84's avatar
    C4L84
    Advocate II
    4 years ago

    OK, thank you so much for your help. It's really appreciated!

  • C4L84's avatar
    C4L84
    Advocate II
    4 years ago

    Hi tamerj1 - this is very almost the solution! However when the month parameter is changed to 1 the measure doesn't work as anticipated:

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi C4L84 
    Yes I know that. Originally I used the Calendar Year Column then shifted the year one year back. But I realized that the calendar year column is just a fixed 0 value everywhere so I used the Finanicial year column instead which is not accurate but returned some results anyway. It is of if I create a new [Year] calculated column with the correct values or you willfix that from your data source and send me the updated data?

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    C4L84 
    It worked with you but I was wrong. Actually I was wrong, the code had a small glitch. You can still use the financial year with no problems

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