Forum Discussion

jasemilly's avatar
jasemilly
Icon for Helper III rankHelper III
5 years ago
Solved

using variables in function TotalYTD

Hi I am trying to create a measure to show % increase/decrease between the current rolling year, sum of values one whole year ending today, against the previous.

I have been trying to use TOTALYTD, I don't see to be able to pass a variable into the function

var EndDay  = "06/10" /*day(today()) & "/" & month(today()) */

var CurYear = TOTALYTD([Vehicles],Dates[Date], EndDay )
 
It gives an error  TRUE?FALSE expression does not specify a column???
 
If I hardcode "06/10" in the function it all works fine 

 
 
At the moment I am using the function  DatesInPeriodbut this seems to have an issue with the leap year  the value is a little out but fine when is say -366

CALCULATE (
[Vehicles],
DATESINPERIOD ( 'dates'[Date], TODAY(), -365, DAY )
)
 
thanks for all help


Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    jasemilly  How about using YEAR instead of DAY in DATESINPERIOD function? Just like below. This will work well without the leap year issue.

    CALCULATE (
[Vehicles],
DATESINPERIOD ( 'dates'[Date], TODAY(), -1, YEAR )
)

     

    Reference: https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax

     

    Best Regards,

    Community Support Team _ Jing Zhang

    If this post helps, please consider Accept it as the solution to help other members find it.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    jasemilly , Try like

    CALCULATE (
    [Vehicles],DATESBETWEEN('dates'[Date], date(year(today())-1,month(today()),day(today())), TODAY())
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Well, you can't pass a variable to this function. It has to be a true constant. (Funnily enough, variables in DAX are contant once defined 🙂

     

    Please read this: https://dax.guide/totalytd/

     

    Marco Russo does not recommend using the TOTALYTD function. There's a whole article about this. Please use DATESYTD instead with a suitable expression under CALCULATE.

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    jasemilly  How about using YEAR instead of DAY in DATESINPERIOD function? Just like below. This will work well without the leap year issue.

    CALCULATE (
[Vehicles],
DATESINPERIOD ( 'dates'[Date], TODAY(), -1, YEAR )
)

     

    Reference: https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax

     

    Best Regards,

    Community Support Team _ Jing Zhang

    If this post helps, please consider Accept it as the solution to help other members find it.