Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can't get any DAX YTD functions to perform correctly Expand / Collapse
Author
Message
Posted Thursday, May 16, 2013 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:36 AM
Points: 2, Visits: 49
Hi,

I am struggling to make any progress with time intelligence functions in SSAS Tabular (or indeed in PowerPivot). I have even created a very simple setup to try to get this to work, but to know avail. Here are some details:

2 tables:
Dates dimension table, with all dates I need, with PK as Date.
Fact table, containing Date as FK, a value called AccountNumber as a dimension and a field containing Amount. These are joined, and the Date table is marked as a date table. Date is not unique in the fact table, since there is a secondary dimension.

First method I tried is simple: =TOTALYTD(sum([Amount]);Dates[Date]). This executes, but is identical to the Amount field (i.e. no aggregation by dates)

Second method I tried is similar and gives the same result: =CALCULATE(SUM([Amount]);DATESYTD(Dates[Date]))

I also tried using the ALL filter on the Dates table: =CALCULATE(SUM([Amount]);DATESYTD(Dates[Date]);ALL(Dates))

I can get it to work for the AccountNumber dimension by including an ALLEXCEPT clause: =CALCULATE(SUM([Amount]);DATESYTD(Dates[Date]);ALLEXCEPT(Budget_Values;Budget_Values[AccountNumber]))

So when I slice by AccountNumber it works fine. But what if I want to slice by another dimension (once I add them)? Or even by two dimensions at the same time? As far as I can see, I would need to create the YTD logic for every slicing dimension and every combination of slicing dimensions!

I want a method that allows me to calculate YTD values for a given measure, regardless of how I am slicing. I have got this working fine in MDX, but need to get it working in DAX to allow integration with PowerView etc.

Any thoughts?
Post #1453516
Posted Tuesday, May 21, 2013 12:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:36 AM
Points: 2, Visits: 49
OK, I've solved it myself. In the interests of sharing this with anyone else facing the same problem here is what was going wrong.

I was trying to create the YTD calculation as a row level function, where it should of course be an aggregate function and therefore be entered at the bottom of the table rather than in a row.

So my function is something similar to:
=TOTALYTD(sum([Amount]);'Dates'[Date])

Post #1454827
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse