Dynamic Period Label
Hi Team! To the best of my abilities, I have researched this and unable to find a solution for my scenario. I have a DimDate and I have shared schema below. This is a 454 period calendar. I would like a Dynamic column via m-query with below logic: 1. Label current period = IF [TODAY] "P0" (use [PERIODSTARTDATE] and [PERIODENDDATE]) 2. Based on 1 above, determine "P-3", "P-2", "P-1", "P0", "P+1", "P+2", "P+3" and so forth The goal here is to aggregate dates into "PERIODLABELS" that represent the planning time horizons of our business. To help with context, this is how it would present in Excel: Help would be greatly appreciated! PERIODENDDATE FISCALPERIODSORT FISCALPERIOD FISCALQUARTER PERIODSTARTDATE FISCALQUARTERSORT FISCALYEAR DATES Year Quarter Week of year Start of week Start of month CALENDARQTRYEAR TODAY Week of fiscal year FISCALPERIODNUM Month name FISCALYEARSHORT PERIODSHORT PERIODNAME This Week 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 27 6/29/2015 ######## 2016-Q1 8/18/2024 1 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 27 6/29/2015 ######## 2016-Q1 8/18/2024 1 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 27 6/29/2015 ######## 2016-Q1 8/18/2024 1 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 27 6/29/2015 ######## 2016-Q1 8/18/2024 1 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 27 6/29/2015 ######## 2016-Q1 8/18/2024 1 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ########
