Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
wmeads001
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
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.

 

3 REPLIES 3
amitchandak
Super User
Super User

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

@wmeads001 ,if you need more help make me @

Appreciate your Kudos.

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.