topic DAX TOTAL YTD problem in Developer

DAX TOTAL YTD problem

Gregs138 — Wed, 07 Jun 2017 01:07:35 GMT

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 missing

Example 2

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

Re: DAX TOTAL YTD problem

Gregs138 — Thu, 08 Jun 2017 18:55:36 GMT

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()))

Re: DAX TOTAL YTD problem

Eric_Zhang — Fri, 09 Jun 2017 07:19:59 GMT

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

Re: DAX TOTAL YTD problem

Anonymous — Tue, 27 Mar 2018 18:31:52 GMT

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