Dynamic Period Label

[majans]

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

########

[DataNinja777]

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
    SequentialNumber

Next, 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,

[majans]

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?