Calculate YTD

[Anonymous]

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 ? 

[hnguy71]

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]

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]

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