Rolling 13 Months data- Multiple Date columns

[ankitg4688]

Hi,

I have a Master Calendar with below columns

Calendar Date

Year

Month

Period (Year & Month) as Year_Month

And 1 fact table which has

Product Start Date

Product_Start_Year_Month as Year_Month

Product_ID_Start

Another fact table which has

Product End Date.

Product_End_Year_Month as Year_Month

Product_ID_End

I need to calculate

[count (Product) as per Product Start Date (Calendar current month)- count (Product) as per Product End Date (Calendar Previous Month) ] for 13 rolling months of Master Calendar. Can anybody help?

[dan_sullivan]

create a flag in your master calendar then use set analysis in your count

ADD THIS TO YOUR CALENDAR SCRIPT:

IF(CalendarDate >= AddMonths(MonthStart(Today()),-12) AND CalendarDate <= Today(),1,0) AS [Rolling 13MO Flag]

CREATE SET ANALYSIS MEASURE

COUNT({$<[Rolling 13MO Flag]={'1'}>}product)

[dan_sullivan]

you also need to have 2 master calendars (one for each fact table) or do a link table to accommodate a single calendar

[ankitg4688]

Thanks for your reply Dan. I will elaborate on this issue.

I am taking 5 years of data and there will be Year, Year_Month, Quarter and Month filters for user selection. My expression for rolling 13 months is

count({ Product_Start_Year_Month = { ">=date(addmonths(Date(date#(max(Product_Start_Year_Month),'YYYYMM'),'YYYYMM')12),'YYYYMM')) <= $(=date(addmonths(date#(max(Product_Start_Year_Month},'YYYYMM'),'YYYYMM')12),'YYYYMM'))" } Product_ID_Start )

which gives me correct rolling data in line chart if user selection filter is  "Product_Start_Year_Month" (belong to same table). But, when I use Year or Year_Month (from master calendar) it doesn't role (provided there is association)

Same, is the case with Product_End_Year_Month.

?

I tried your suggestion of link table but still it didn't work. Can you suggest on this?