locked
How calculating YTD measure with a time dimension RRS feed

  • Question

  • Hi,

    I've an amount from a fact table and a time dimension (year and month both selectable with the related filter). I need to calculate a YTD measure selecting one year and one month (considering the month between january and the selected month).

    Any suggests to me, please? Many thanks

    Wednesday, December 7, 2011 5:57 PM

Answers

  • You should be able to create a calculated measure on the fact table using TOTALYTD like this:

    TOTALYTD ( 
                       SUM ( yourFactTable[yourAmount])
                       ,timeDimension[Date] 
                     )
    

    This assumes you have a date in the fact table that is related to the time dimension.  And your time dimension has a continuous list of dates for all years covered in the fact table. 

    With that, you should be able to drag years and months from the time dimension onto your pivot table and get the YTD amounts from this calculated measure.

    Hope this helps.


    Brent Greenwood, MS, MCITP, CBIP
    Thursday, December 8, 2011 3:48 PM
    Answerer