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...

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors