Dynamic calendar

[wmeads001]

Hi   I am trying to create a dynamic calendar at the Month level (There is no need for day level)  I have created a master calendar with no problem and I am now trying to create the Dynamic period filter table that will link to the Master calendar and filters can be applied to the required monthly periods in a visualisation

The dynamic period filter table will look like this.  (Only some values shown)

Expected outcome for a sample of Periods
BasePeriod  PeriodMeasure  PeriodKey
201901        YTD                   201901
201902        YTD                   201901
201902        YTD                   201902
201903        YTD                   201901
201903        YTD                   201902
210903        YTD                   201903
201904        YTD                   201901
201904        YTD                   201902
201904        YTD                   201903
201904        YTD                   201904
201901        Last Year YTD   201801
201902        Last Year YTD   201801
201902        Last Year YTD   201802
201903        Last Year YTD   201801
201903        Last Year YTD   201802
201903        Last Year YTD   201803
202002        Last 3 months  201912
202002        Last 3 months  202001
202002        Last 3 months  202002
202003        Last 3 months  202001
202003        Last 3 months  202002
202003        Last 3 months  202003

I am able to generate the PeriodKey for a number of PeriodMeasures using a fixed BasePeriod using this Dax

PeriodMeasureFilter =

VAR Today = Date(2018,05,01)

//VAR Today = Date(StartYear,01,01)

VAR ThisYear = YEAR(Today) //Date(StartYear,01,01))

VAR ThisMonth = MONTH(Today)

VAR ThisPeriod = ThisYear *100 + ThisMonth

VAR MinMonth = YEAR(DATE(StartYear,01,01)) *100 +MONTH(01)

VAR MaxMonth = YEAR(DATE(EndYear,12,31)) *100 +MONTH(12)

RETURN

SELECTCOLUMNS(

UNION(

// Last 2 Months

ADDCOLUMNS(

GENERATE(

SELECTCOLUMNS({"Last 2 Months"}, "PeriodMeasure",[Value]) ,

GENERATESERIES(

ThisYear*100 + ThisMonth-1,

ThisYear*100 + ThisMonth)

),"PeriodKey" , [Value] , "BasePeriod" , ThisPeriod )

,

// Last 3 Months

ADDCOLUMNS(

GENERATE(

SELECTCOLUMNS({"Last 3 Months"},"PeriodMeasure",[Value]) ,

GENERATESERIES(

// DATE(ThisYear , ThisMonth - 2 , 1) ,

ThisYear*100 + ThisMonth-2,

ThisYear*100 + thisMonth)

),"PeriodKey",[Value],"BasePeriod", ThisPeriod )

,

// Current Year

ADDCOLUMNS(

GENERATE(

SELECTCOLUMNS({"Current Year"},"PeriodMeasure",[Value]) ,

GENERATESERIES(

ThisYear*100 + 01 ,

ThisYear*100 +ThisMonth)

),"PeriodKey",[Value],"BasePeriod",ThisPeriod)

,

// Prior Year

ADDCOLUMNS(

GENERATE(

SELECTCOLUMNS({"Prior Year"},"PeriodMeasure",[Value]) ,

GENERATESERIES(

(ThisYear -1)*100 + 01,

(ThisYear -1)*100 + ThisMonth )

),"PeriodKey",[Value],"BasePeriod",ThisPeriod )

,

//YTD

ADDCOLUMNS(

GENERATE(

SELECTCOLUMNS({"YTD"},"PeriodMeasure",[Value]) ,

GENERATESERIES(

ThisYear*100 + 01,

ThisYear*100 + ThisMonth )

),"PeriodKey",[Value],"BasePeriod",ThisPeriod )

) ,

"BasePeriod" , [BasePeriod] ,

"PeriodKey" , [PeriodKey] ,

"PeriodMeasure" , [PeriodMeasure]

)

I would like to generate all the PeriodKey's for all the PeriodMeasure,s for all the BasePeriod's between the first period in StartYear and the end period in Endyear which are two paramaters used to generate the main Calender file

Any help would be appreciated.

[amitchandak]

What is objective of this calendar. Are you trying to YTD, LYD or rolling months? Can time intelligence help you in that?

[amitchandak]

@wmeads001 ,if you need more help make me @

Appreciate your Kudos.

[Greg_Deckler]

So you want the first table displayed between StartYear and EndYear and you want Base Periods

Table = 
  VAR __Years = GENERATESERIES([StartYear],[EndYear],1)
  VAR __Months = { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12")
  VAR __Table = 
    ADDCOLUMNS(
      GENERATE(__Years,__Months)
      "__Base Period",[Value1] & [Value2]
    )
RETURN
  SELECTCOLUMNS(__Table,"Base Period",[__Base Period])