cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

DAX TOTAL YTD problem

Hi there, trying to solve what should seem like a fairly straightforward reporting/visual problem.  I am trying to compare LY Total Sales to CY Total Sales using a line chart by month

 

The problem is that when I use am expression like this:

 

Example1:

YTD Sales = TOTALYTD('fact'[Total Sales],'DateDimension'[Date])

I get blank results.

 

Oddly if I use :

Example 2

YTD Sales = TOTALYTD('fact'[Total Sales], 'fact'[DateOfSale])

 

I get what appear to be good result for CY, but some rason now LY has a big gap in it. 

 

 

My model is pretty standard with a one to many dimDate ->facttable design joined on date.  Every example I have looked at seems to say my first example is correct, however it does not work at all.  The second example, I typically don't do and would expect it to be a little odd anyway, but not sure why example one is not working.  I have done this type of YTD expression before and I feel it might be something else besides the expression, maybe a relationship, data type, some other issue.  Could be I am just really tired lol.  But I should be able to name the Date field from the Date dimension and this would work.  I have never had to call the date field from the fact table itself.

 

Anyway any feedback or advice would be great.  Been banging my head against this one all day and not sure what I am doing wrong or what setting I might be missing.  TY!

 

 

Results of Example 1 expression( YTD TOTAL is completely missing)

YTD Sales = TOTALYTD('fact'[Total Sales],'DateDimension'[Date])

 

YTD completely missingYTD completely missing

 

Example 2

YTD Sales = TOTALYTD('fact'[Total Sales], 'fact'[DateOfSale])

LY vs YTD.JPG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Helper I
Helper I

Re: DAX TOTAL YTD problem

Just some follow up on this for anyone that is having the same problem.

 

The solution ended up being to add a filter to the YTD() function.  It is stated that it is an optional filter, however it seems to be rather necessary or else your YTDTotal will carry the last total(today) until the end of the year.  This is a little buggy imo but manageble with the custom filter parameter.

 

Here was my fix:

 

YTD Sales = TOTALYTD(SUM(SalesTable[SalesAmount]),'Order Date'[Date],filter('Order Date',[Date] < NOW()))

View solution in original post

Highlighted
Microsoft
Microsoft

Re: DAX TOTAL YTD problem


@Gregs138 wrote:

Just some follow up on this for anyone that is having the same problem.

 

The solution ended up being to add a filter to the YTD() function.  It is stated that it is an optional filter, however it seems to be rather necessary or else your YTDTotal will carry the last total(today) until the end of the year.  This is a little buggy imo but manageble with the custom filter parameter.

 

Here was my fix:

 

YTD Sales = TOTALYTD(SUM(SalesTable[SalesAmount]),'Order Date'[Date],filter('Order Date',[Date] < NOW()))


@Gregs138

Thanks for your sharing. You can mark your reply as solution to close this thread. People who may have the same question can benefit from the solution.

View solution in original post

3 REPLIES 3
Highlighted
Helper I
Helper I

Re: DAX TOTAL YTD problem

Just some follow up on this for anyone that is having the same problem.

 

The solution ended up being to add a filter to the YTD() function.  It is stated that it is an optional filter, however it seems to be rather necessary or else your YTDTotal will carry the last total(today) until the end of the year.  This is a little buggy imo but manageble with the custom filter parameter.

 

Here was my fix:

 

YTD Sales = TOTALYTD(SUM(SalesTable[SalesAmount]),'Order Date'[Date],filter('Order Date',[Date] < NOW()))

View solution in original post

Highlighted
Microsoft
Microsoft

Re: DAX TOTAL YTD problem


@Gregs138 wrote:

Just some follow up on this for anyone that is having the same problem.

 

The solution ended up being to add a filter to the YTD() function.  It is stated that it is an optional filter, however it seems to be rather necessary or else your YTDTotal will carry the last total(today) until the end of the year.  This is a little buggy imo but manageble with the custom filter parameter.

 

Here was my fix:

 

YTD Sales = TOTALYTD(SUM(SalesTable[SalesAmount]),'Order Date'[Date],filter('Order Date',[Date] < NOW()))


@Gregs138

Thanks for your sharing. You can mark your reply as solution to close this thread. People who may have the same question can benefit from the solution.

View solution in original post

Highlighted
New Member

Re: DAX TOTAL YTD problem

I am don't understand why it is nesessary to use another dateset (Order Date[Date]) in the TotalYTD formula? Why can't you use the date column in your Sales Table? I have highlighted this below and would appreciate if you can share your knowlege:

 

TOTALYTD(SUM(SalesTable[SalesAmount]),'Order Date'[Date]

 

Tnx

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors