PowerPivot DAX function TOTALYTD not giving cumulative sum

Michael Bekiares
Michael Bekiares used Ask the Experts™
on
I'm trying to create a Year To Date cumulative sum column in my pivot table using the DAX function TOTALYTD.
For some reason I am not getting a cumulative sum.
I just the same result as the cell in the column it's supposed to be summing to date.

Here is my data model
Model
Here is the date relationship
2.PNG
Here is my measure
Measure
And here is what I see in the pivot table
Pivot Result
I'm stumped.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Tom FarrarConsultant

Commented:
Did you pull in the wrong field to the pivot?  Looks like the name of the measure is different from what you show on the pivot.
Michael BekiaresBI Developer

Author

Commented:
Not sure I follow.  The measure is called ytd_gms, which is shown in the pivot and the measure editor.
Tom FarrarConsultant

Commented:
Yep, I missed that.  Sorry.
Tom FarrarConsultant

Commented:
For example, the following formula specifies a (fiscal) year_end_date of 6/30 in an EN-US locale workbook.

DAX

Copy
=TOTALYTD(SUM(InternetSales_USD[SalesAmount_USD]),DateTime[DateKey], ALL(‘DateTime’), “6/30”)  
In this example, year_end_date can be specified as “6/30”, “Jun 30”, “30 June”, or any string that resolves to a month/day. However, it is recommended you specify year_end_date using “month/day” (as shown) to ensure the string resolves to a date.

This DAX function is not supported for use in DirectQuery mode. For more information about limitations in DirectQuery models, see https://go.microsoft.com/fwlink/?LinkId=219172.
Michael BekiaresBI Developer

Author

Commented:
Hmm.  Let's try this.  I'm attaching a worksheet we can use to try to debug the issue.  Perhaps you can coax the measure code into producing the right results.
2019-12-24-For-Support.xlsx
Michael BekiaresBI Developer

Author

Commented:
In this case I used both commonly cited formulas to produce a year to date running total.  Both give the same result - no cumulative sum.
Michael BekiaresBI Developer

Author

Commented:
Here's some more interesting information.
If I explicitly declare the dates with CALENDAR() I get the same results for all years - even if the dates are for only a specific year.

Formula:
CALCULATE(sum(brand_dashboard_facts[gms]),CALENDAR ("01-01-2018","12-31-2018"))

Notice that I declared only dates in 2018.
And yet....
7.PNG
So it seems to me like it's not looking at the date information AT ALL.
Tom FarrarConsultant

Commented:
Chew on this for a while..
2019-12-24-For-Support--1-.xlsx
Consultant
Commented:
With your original loaded file I just moved the date hierarchy into the row field replacing what you had in there.  Seems to work fine for both of your measures.
2019-12-24-For-Support--2-.xlsx
Michael BekiaresBI Developer

Author

Commented:
Yes - it seems you got it to work.  I'd like to try to unpack this so I can understand why.  What was causing the original to NOT sum, i.e. what was the DAX engine "looking at" as the return from the DATESYTD(Calendar[Date])) in the original scenario?
Tom FarrarConsultant

Commented:
I believe the simple answer is when you have a dim date table, the date should always come from that table and not the fact table.  I am a bit new to DAX also, so there may be someone here (or at more specific DAX-related sites), that can give you a better answer.  I'll keep investigating as I learn more about PowerPivot and DAX.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial