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

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
majans
Frequent Visitor

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: 

majans_0-1723944833331.png

 

 

Help would be greatly appreciated! 

 

 

PERIODENDDATEFISCALPERIODSORTFISCALPERIODFISCALQUARTERPERIODSTARTDATEFISCALQUARTERSORTFISCALYEARDATESYearQuarterWeek of yearStart of weekStart of monthCALENDARQTRYEARTODAYWeek of fiscal yearFISCALPERIODNUMMonth nameFISCALYEARSHORTPERIODSHORTPERIODNAMEThis Week
7/31/20150Period 1Q1########02016########20153276/29/2015########2016-Q18/18/202411Jul16P1P1-Jul########
7/31/20150Period 1Q1########02016########20153276/29/2015########2016-Q18/18/202411Jul16P1P1-Jul########
7/31/20150Period 1Q1########02016########20153276/29/2015########2016-Q18/18/202411Jul16P1P1-Jul########
7/31/20150Period 1Q1########02016########20153276/29/2015########2016-Q18/18/202411Jul16P1P1-Jul########
7/31/20150Period 1Q1########02016########20153276/29/2015########2016-Q18/18/202411Jul16P1P1-Jul########
7/31/20150Period 1Q1########02016########2015328################2016-Q18/18/202421Jul16P1P1-Jul########
7/31/20150Period 1Q1########02016########2015328################2016-Q18/18/202421Jul16P1P1-Jul########
7/31/20150Period 1Q1########02016########2015328################2016-Q18/18/202421Jul16P1P1-Jul########
7/31/20150Period 1Q1########02016########2015328################2016-Q18/18/202421Jul16P1P1-Jul########
7/31/20150Period 1Q1########02016########2015328################2016-Q18/18/202421Jul16P1P1-Jul########
7/31/20150Period 1Q1########02016########2015328################2016-Q18/18/202421Jul16P1P1-Jul########
7/31/20150Period 1Q1########02016########2015328################2016-Q18/18/202421Jul16P1P1-Jul########
2 REPLIES 2
DataNinja777
Super User
Super User

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,

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?

 

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.