Forum Discussion

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

TotalYTD function returning error

Hey All, I have a column with values and a column with dates (image below). I'm trying to create a simple measure calculating the YTD sum of the values. I tried the following measure:  ODC Expense...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  akkitek ,

    You can try to change the formula to the following form:

    ODC Expense YTD =
CALCULATE(SUM('FACT_Fin_Data_by_Mo'[ODC Expense]),
FILTER(ALL('Calendar'),'Calendar'[Date]<=TODAY()&&'Calendar'[Date]>=DATE(YEAR(TODAY()),1,1)))

    Result：

     

    Best Regards,

    Liu Yang

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

akkitek's avatar
akkitek
Icon for Helper III rankHelper III
5 years ago

Hey Ashish_Mathur ,
Thanks for your reply. I created a calendar table with proper date entries using the calendar function and built the relationship. I created your suggested measures, The error is gone but it returns 0 which is not very useful. Am I missing something here?

Looking foward to hearing from you:)

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

Hi,

Share the link from where i can download your PBI file.

  • akkitek's avatar
    akkitek
    Icon for Helper III rankHelper III
    5 years ago

    Hey Ashish_Mathur ,

    Here's the link to the file:
    https://drive.google.com/file/d/1hYDaBCJQNGfp1XGeNzAxeVAbGiNlaBMX/view?usp=sharing

    When i use the following measures, I get 0: 

    ODC_expenses = SUM(FACT_Fin_Data_by_Mo[ODC Expense])

    YTD ODC expenses = calculate([ODC_expenses],datesytd(calendar[date],"31/12"))



    and When i use this measure, I get the error: 

    ODC Expense YTD = TOTALYTD(SUM(FACT_Fin_Data_by_Mo[ODC Expense]),FACT_Fin_Data_by_Mo[Month])
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      The YTD measure will work only if you select a Date dimension.  Download my PBI file from here.

      Hope this helps.

      • akkitek's avatar
        akkitek
        Icon for Helper III rankHelper III
        4 years ago

        Gotcha! Thanks for the help!