Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Custom YTD with different start date

Hi,

 

I am going nuts trying to find a solution to my qustion, so I hope someone here can helt me!

What I would like to should be simple, I would like to create a YTD TY, YA and 2YA where the starts dates are known but different for each year and I want to have it in my Calendar Table...  

 

It would look something like this:

 

YTD TY: 31-01-2021 to MAX Date

YTD YA: 02-02-2020 to MAX Date -1 Year

YDT 2YA: 03-02-2019 to MAX Date -2 Year

 

Is it possible? 

Many thanks for your help!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Try to replace ALL(CalendarTable[Date]) with CalendarTable[Date] in all DATESBETWEEN functions.

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous You can use DATESBETWEEN function where you point out start and end dates for a period.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    Thanks for quick feedback! 

    It solves my YTD TY:

     

    YTD TY test = CALCULATE([Value], DATESBETWEEN(CalendarTable[Date],"31-01-2021",MAXX(Periods,Periods[EndOfWeek])))
     
    But I cant get the YTD YA and 2 YA to work with the same logic... any idea on how to solve that?
    Thanks,
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Anonymous There are several ways. For example, you can use DATE function like this:

      YTD YA = var varLastDate=MAXX(Periods,Periods[EndOfWeek])

      return CALCULATE([Value], DATESBETWEEN(CalendarTable[Date],"02-02-2020",DATE(YEAR(varLastDate)-1,MONTH(varLastDate)+1,1)-1))

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Many thanks Anonymous ! That worked perfect! 

    Now they all 3 work as measures but I need them all in the Calender Table, I have tried some Swith and IF but I cant get it to work at all... any good solution here? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      If I got you right you can try something like this:

       

      YTD type column=var varLastDate=MAXX(Periods,Periods[EndOfWeek])

      return 

      SWITCH(TRUE(),

      CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"01-01-2021",varLastDate), "YTD TY"

      CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"02-02-2020",DATE(YEAR(varLastDate)-1,MONTH(varLastDate)+1,1)-1), "YTD YA"

      CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"03-02-2019",DATE(YEAR(varLastDate)-2,MONTH(varLastDate)+1,1)-1), "YTD 2YA")

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Many thanks Anonymous ! 

        I get a strange error saying "DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument."

         

        EndOfWeek and Date are both date... 
         
        YTD Test =
         
        VAR varLastDate=MAXX(Periods,Periods[EndOfWeek])

        Return
        SWITCH(
        TRUE(),
        CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"31-01-2021",varLastDate), "YTD TY",
        CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"02-02-2020",DATE(YEAR(varLastDate)-1,MONTH(varLastDate)+1,1)-1), "YTD YA",
        CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"02-02-2019",DATE(YEAR(varLastDate)-2,MONTH(varLastDate)+1,1)-1), "YTD 2YA"
        )