Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate YTD

Hi

 

I am having my fact at month level granularity and hence I have MonthId in my fact table. I have mapping on MonthId column between fact and date dimension table. I want to check is it possible to create YTD measure based on date dimension with month level granularity ? 

Need Help

3 Replies

  • hnguy71's avatar
    hnguy71
    Super User
    7 years ago

    Is your MonthID a unique identifier and pairs 1 to 1 with your date table? What about your date table? What's the start and end dates? How did you create / retrieve the dates? Where is the data being stored? How do you know which year the dataset belongs to?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Yes, MonthId is a unique key in dim table. Mapping is there between fact and dimension based on MonthId column. There are other columns also present i.e. MonthName, Year, Quarter...

      • hnguy71's avatar
        hnguy71
        Super User
        7 years ago

        As long as you have a foreign key to the dim table it's possible.

         

        SampleYTD1 = TotalYTD(Sum(FROM WHERE), Date Table[Date Column], Any Filters)

         

        SampleYTD2 = Calculate(Sum(FROM WHERE), Datesbetween(Date Table, Begin Date, End Date))

         

        I prefer using the second YTD measure for better control