Forum Discussion
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 | ######## |
2 Replies
Hi majans ,
You can generate the desired output by creating sequential numbering for the FISCALYEAR and FISCALPERIODNUM column in your 454 calendar as shown below.
YearMonthSequential = VAR CurrentYear = '454_Calendar'[FISCALYEAR] VAR Current454Month = '454_Calendar'[FISCALPERIODNUM] VAR SequentialNumber = (CurrentYear - MIN('454_Calendar'[FISCALYEAR])) * 12 + Current454Month RETURN SequentialNumberNext, create an additional calculated column to classify dates into P+1, P+2, P+3, and so on based on the sequential numbering of YearMonth.
Best regards,
Hi DN77,
Apologies for delay.
This is a viable solution so thank you!
If I wanted this within my DateDim, how could you achieve same as a custom column?
