Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akkitek
Helper III
Helper III

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 YTD = TOTALYTD(SUM(FACT_Fin_Data_by_Mo[ODC Expense]),FACT_Fin_Data_by_Mo[Month])
but I get the following error:
akkitek_2-1632435036288.png

I've tried many diffrent variations of this calculation but keep getting the same error. 

 

akkitek_0-1632434915208.png

Any help is appreciated. Thanks:)

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1632900602241.png

 

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

View solution in original post

24 REPLIES 24
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1632900602241.png

 

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

This worked! Thanks for the help:)

parry2k
Super User
Super User

@akkitek it is a pretty simple thing and seems like we are making it complicated. It will be easier if you share pbix file, remove sensitive information before sharing. It is very hard to provide a solution without looking at the details.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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])
v-yangliu-msft
Community Support
Community Support

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.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery#limitations-of-direct...

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:

https://community.powerbi.com/t5/Desktop/PREVIOUSMONTH-produces-quot-An-invalid-numeric-representati...

https://community.powerbi.com/t5/Desktop/Error-with-SAMEPERIODLASTYEAR-an-invalid-numeric-representa...

 

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 @v-yangliu-msft ,
Thanks for your reply.
1) I don't think I'm using DQ mode as this is a Web datasource and doesn not support DQ

akkitek_0-1632753911606.png

2) 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 too

akkitek_1-1632754716958.png

Looking forward to hearing from you. Thanks!

 

AndreiK15
Helper II
Helper II

Hi,

 

Try the following:

 

CALCULATE(SUM(Table[ODC Expense],DATESYTD(Table[Month]))

 

AndreiK15_0-1632459289290.png

 

 

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 too

akkitek_2-1632755873485.png

 

 

Looking forward to hearing from you. Thanks!

parry2k
Super User
Super User

@akkitek few questions.

 

- did you mark the calendar table as a date table?

- can you share the screenshot of the error?

- how you are trying to visualize the data?

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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 too

akkitek_0-1632755175721.png

 

Looking forward to hearing from you. Thanks!

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@Ashish_Mathur did you even took the time to read my reply?  



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@Ashish_Mathur I didn't ask about my post, I asked about how your reply is different than mine?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

How would i know that unless i read the contents in the link of your post?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User
Super User

@Ashish_Mathur not sure how your solution is different than what I replied. Just wondering, did I missed something?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I do not know.  I have not checked the links in your post.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?

akkitek_1-1632755763824.png

Looking foward to hearing from you:)

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.