Measure on different Date against Generic Date in Chart

[mhmmd_srf]

Hi All,

I have Fact table called Request having Opened_Date, Opened_Month,Closed_Date and Closed_Month. Closed_Date/Moth and Open_Date/Month can be different.

I need to create a line chart with two Metrics:

Open_Count: Count({$<Opened_Month ={Last 24 Months}>} Req_number)

Closed_Count: Count({$<Closed_Month={Last 24 Months}>} Req_number)

But in X axis we need to have a generic Month that is Prior_Months. Which we have created using below script:

Last_Month:

Load

  Date(MonthStart(Today(),-RowNo()+1), 'YYYY-MMM') as Prior_Months

AutoGenerate 26;

Could you please help on this.

Thanks,

Sarif

[swuehl]

You can use a

Canonical Date

[migueldelval]

Hi Sarif,

First I think That you need link both tables (Prior_month and Fact_table). Maybe you can calculate a fiel in fact table or use a Master calendar to make it.

Calendars

Regards

Miguel del Valle