Forum Discussion

wmeads001's avatar
wmeads001
Regular Visitor
6 years ago

Dynamic calendar

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.

 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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])
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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