cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
campelliann
Helper II
Helper II

Cannot understand DatesYTD behavior with/without hierarchies

Hi people,


So I have to calculate the accumulated revenue for 2021 and I have a bar chart with the months at the x axis.

I am using Calculate( sum (Table[revenue]), DatesYTD(Calendar[YearMonth].[Date])

 

I am using a Date hierarchy at the month level. I dont understand why it makes a different using .[Date] or not at the end of DatesYTD. With, I get the desired result an accumulated revenue, If I dont use .Date it only gives me the revenue for the particular month (no cumulative) which does not make sense to me, because the "Table" with revenues has always the first day of the month(.Date returns the full calendar)


Thanks

Thanks

1 ACCEPTED SOLUTION

Hi and many thanks for your help. I have already solved this without the YTD. Still I just used the .date because without it would give me each month revenue (not the cumulative). Although I do not understand why...

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @campelliann 

Thanks for reaching out to us.

Please try this measure

accumulateTotal = CALCULATE(SUM('Table'[revenue]),ALL('Table'),DATESYTD('Calendar'[YearMonth]))

vxiaotang_4-1635318471311.png

-

usually, we don't need to use .[Date] in dax, it only work in date hierarchy row context.

Kindly refer to https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time

 

I also create a sample for your reference, 

test1 = Calculate(SUM('Table'[revenue]), DatesYTD('Table'[Date]))
test2 = Calculate(SUM('Table'[revenue]), DatesYTD('Table'[Date].[Date]))
1, when you don't use date hierarchy, the first measure test1 works ok,
vxiaotang_0-1635317771847.png
2, when you use date hierarchy, the second measure test2 works ok,
vxiaotang_1-1635317866391.png
 
 

Best Regards,

Community Support Team _Tang

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

AlexisOlson
Super User
Super User

I recommend never using the .[Date] suffix (or other similar ones) at all. They tend to cause confusion.

 

I would write it like this:

CALCULATE ( SUM ( Table[revenue] ), DATESYTD ( Calendar[Date] ) )

 

Hi and many thanks for your help. I have already solved this without the YTD. Still I just used the .date because without it would give me each month revenue (not the cumulative). Although I do not understand why...

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.