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