Forum Discussion
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:
but I get the following error:
I've tried many diffrent variations of this calculation but keep getting the same error.
Any help is appreciated. Thanks:)
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
24 Replies
akkitek for time intelligence, it is a best practice to add a calendar dimension in your model, and you can follow my blog post here to add one and then use Date column from this dimension in TOTALYTD DAX function.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Thank you for your reply and nice blog! I tried this, I created the calendar table using 'Calendarauto' function and joined the dates with the month column in my main table. And created a measure like, TotalYTD(SUM(odc_expense),Calendar[date]) but still get the same error.
Hi,
Assuming the Month column has proper date entries, create a Calendar Table and build a relationship from the Month column to the Date column of the Calendar Table. To your visual, drag the Date from the Calendar Table. Write this measure:
ODC_expenses = SUM(FACT_Fin_Data_by_Mo[ODC Expense])
YTD ODC expenses = calculate([ODC_expenses],datesytd(calendar[date],"31/12"))
Hope this helps.
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:)
Hi,
Share the link from where i can download your PBI file.
Ashish_Mathur not sure how your solution is different than what I replied. Just wondering, did I missed something?
I do not know. I have not checked the links in your post.
Ashish_Mathur I didn't ask about my post, I asked about how your reply is different than mine?
How would i know that unless i read the contents in the link of your post?
Ashish_Mathur did you even took the time to read my reply?
Ashish_Mathur anyhow, good luck but I just asked a simple question if my reply is different than yours so that I can learn from your solution, and I'm not expecting you to read my post. Thanks!
Hey parry2k ,
Yes, my calendar table is a date field.
I am trying to have a Table visual (image below) with ODC Expense and Resource direct cost by Project code. I can set a filter to this visual by using the month column in the filter pane and manually selecting the months until this month but I want to make it dynamic and just create a measure which returns just the YTD values for ODC expense and RDC. The card visual has the YTD ODC expense field and is broken and returns the following error. When I drop the YTD ODC Expense measure in the table visual it breaks tooLooking forward to hearing from you. Thanks!
Hi,
Try the following:
CALCULATE(SUM(Table[ODC Expense],DATESYTD(Table[Month]))
It should work!
Hey AndreiK15 ,
Thanks for your reply. I tried this measure but still get the same error.
I am trying to have a Table visual (image below) with ODC Expense and Resource direct cost by Project code. I can set a filter to this visual by using the month column in the filter pane and manually selecting the months until this month but I want to make it dynamic and just create a measure which returns just the YTD values for ODC expense and RDC. The card visual has the YTD ODC expense field and is broken and returns the following error. When I drop the YTD ODC Expense measure in the table visual it breaks tooLooking forward to hearing from you. Thanks!
Hi akkitek ,
1. Are you using DQ mode, special treatment of date columns (drill down by using year, quarter, month, or day) isn't supported in DirectQuery mode.
2. Does your date table have invalid date and time values exceeding 9999 years?
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#datetime-types
This is the related document, you can view this content:
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.
Hey Anonymous ,
Thanks for your reply.
1) I don't think I'm using DQ mode as this is a Web datasource and doesn not support DQ2) My date table doesn not exceed 9999 years. the latest date in my table is 4/1/2101.
I am trying to have a Table visual (image below) with ODC Expense and Resource direct cost by Project code. I can set a filter to this visual by using the month column in the filter pane and manually selecting the months until this month but I want to make it dynamic and just create a measure which returns just the YTD values for ODC expense and RDC. The card visual has the YTD ODC expense field and is broken and returns the following error. When I drop the YTD ODC Expense measure in the table visual it breaks tooLooking forward to hearing from you. Thanks!
Hey parry2k ,
Sure thing! Here's the link to the file:
https://drive.google.com/file/d/1hYDaBCJQNGfp1XGeNzAxeVAbGiNlaBMX/view?usp=sharing
When i use the following measure, I get 0:ODC Expense YTD = TOTALYTD(SUM(FACT_Fin_Data_by_Mo[ODC Expense]),'Calendar'[Date])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])
