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

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

I tried to build a proper calendar table and here is my latest table. 

 

I used the following functions and by pulling Year on top and Month below in the field, i managed to view some data. However, i noticed that if i pull FY21 into a card visual, it showed blank but i can see numbers for FY22. When i looked at the table, it might be due to the 'total' value is missing in FY21. Do you know what is causing that? both years are using the same formula...

 

YTD FY21 = TOTALYTD([Actual FY21],'Calendar'[Date],"1/31")
YTD FY22 Team Commit = TOTALYTD([Forecast Team Commit],'Calendar'[Date],"01/31")

 

 

Appreciate your advise! thank you!!

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    With TOTALYTD and DATESYTD functions, year-to-date is defined relative to the maximum value of Dates[Date] in the filter context. Since in the filter context of 'total' of FY21 table the maximum year is FY22, TOTALYTD() changed the filter on the calender table to the range of FY22 ie, 1st Feb 2022 - 31st Jan 2023 for which the measure '[Actual FY21]' may not have the values and thus returned blank. Similarly for the card visual, in the filter context, the maximum year is FY22 and '[Actual FY21]' may not have values in that range hence the card is also blank. This is what I think is happening. 

     

    Regards,

    Aditya

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous  

       

      Thanks for your advise!

      In this case, how do I change the formula for FY21 to show the YTD total value in card visual?

       

      Thank you!