May 16, 2013 at 7:20 am
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?
May 21, 2013 at 12:36 am
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])
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply