cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## 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
GENERATE(
SELECTCOLUMNS({"Last 2 Months"}, "PeriodMeasure",[Value]) ,
GENERATESERIES(
ThisYear*100 + ThisMonth-1,
ThisYear*100 + ThisMonth)
),"PeriodKey" , [Value] , "BasePeriod" , ThisPeriod )
,

// Last 3 Months
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
GENERATE(
SELECTCOLUMNS({"Current Year"},"PeriodMeasure",[Value]) ,
GENERATESERIES(
ThisYear*100 + 01 ,
ThisYear*100 +ThisMonth)
),"PeriodKey",[Value],"BasePeriod",ThisPeriod)
,

// Prior Year
GENERATE(
SELECTCOLUMNS({"Prior Year"},"PeriodMeasure",[Value]) ,
GENERATESERIES(
(ThisYear -1)*100 + 01,
(ThisYear -1)*100 + ThisMonth )
),"PeriodKey",[Value],"BasePeriod",ThisPeriod )
,

//YTD
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.

3 REPLIES 3
Super User

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

Super User

@wmeads001 ,if you need more help make me @

Super User

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 =
GENERATE(__Years,__Months)
"__Base Period",[Value1] & [Value2]
)
RETURN
SELECTCOLUMNS(__Table,"Base Period",[__Base Period])
``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...