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...
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!!

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        It is not a must but considered the best practice to make a calendar table using DAX in Power BI for the best utilization of time intelligence functions. 

        You can use CALENDERAUTO()

         

        Calendar Table = CALENDARAUTO(2)

         

        Here the argument '2' specifies the end of the fiscal year as in your case. It then creates the ideal date table by looking at the dates in your data model and the end of fiscal year you provided.

        On a side note, can you please elaborate about the Month column you used here? There may be some issue with it.

         

        Regards,

        Aditya