Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

YTD Function

Hello

 

I am trying to use the TOTALYTD formula as below to create a YTD measure (my year end is January) and when i pull the measure into a table by date, it works. However, when i filter by month or quarter, it appears empty and doesn't work. 

 

Has anyone encountered this before?

 

YTD FY21 Sales = TOTALYTD([Actual FY21],'Calendar'[Date],"01/31")

 

Appreciate your advise! thank you!

8 Replies

  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    Impactful Individual
    5 years ago

    Hi Anonymous ,

     

    TOTALYTD works only if you have the full set of dates in the given time.

     

     

    Mark this as a solution if I answered your question. Kudos are always appreciated.

    Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Tanushree_Kapse 

    Thanks for your reply. I already have the full set of dates in place but still it doesn't work..

    My date table runs from 1 Feb 2020 until 31 Jan 2022, representing two full fiscal year.

     

    Is that considered full set?

     

    thank you!

     

    • Tanushree_Kapse's avatar
      Tanushree_Kapse
      Impactful Individual
      5 years ago

      Hi Anonymous ,

       

      Sorry, I misunderstood your problem at first.

       

      What you can do now is:
      1) Create a seperate column for just the enddates of the particular months.

      2) Create a Calendar Table using this new enddate column, with distinct dates.
          CalendarTable= DISTINCT(Table[enddate])

      3) Then use this table's column for your YTD measure:

      YTD FY21 Sales = TOTALYTD([Actual FY21],'CalendarTable'[enddate],"01/31")

       

      Create relationship between the tables using enddates.

      Format the enddate to MMM-YY and use this column in your visual.

       

      I hope this helps!

       

      Mark this as a solution if I answered your question. Kudos are always appreciated.

      Thanks!

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Tanushree_Kapse 

         

        Thanks for your advise. Let me try that later.

         

        On another note, I manually built my calendar table in excel and load into PowerBI. Is that causing the issue I am facing?

         

        In order to use the time intelligence DAX formula, is it a must to build calendar table within power BI using DAX?

         

        Thank you!!