Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
I've tried many diffrent variations of this calculation but keep getting the same error.
Any help is appreciated. Thanks:)
Solved! Go to Solution.
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
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
This worked! Thanks for the help:)
@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:
and When i use this measure, I get the error:
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 @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
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
Looking 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 too
Looking forward to hearing from you. Thanks!
@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
Looking forward to hearing from you. Thanks!
@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.
@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.
@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?
@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.
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.
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:
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |