topic Re: Dynamic Period Label in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Period-Label/m-p/4105496#M162923 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="181150" data-lia-user-login="majans" class="lia-mention lia-mention-user">majans</a>&nbsp;,</P> <P>&nbsp;</P> <P>You can generate the desired output by creating sequential numbering for the&nbsp;FISCALYEAR and <SPAN>FISCALPERIODNUM</SPAN> column in your 454 calendar as shown below.</P> <LI-CODE lang="markup">YearMonthSequential = VAR CurrentYear = '454_Calendar'[FISCALYEAR] VAR Current454Month = '454_Calendar'[FISCALPERIODNUM] VAR SequentialNumber = (CurrentYear - MIN('454_Calendar'[FISCALYEAR])) * 12 + Current454Month RETURN SequentialNumber</LI-CODE> <P>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.</P> <P>&nbsp;</P> <P>Best regards,</P> Sun, 18 Aug 2024 02:14:17 GMT DataNinja777 2024-08-18T02:14:17Z Dynamic Period Label https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Period-Label/m-p/4105479#M162922 <P>Hi Team!</P><P>&nbsp;</P><P>To the best of my abilities, I have researched this and unable to find a solution for my scenario.</P><P>&nbsp;</P><P>I have a DimDate and I have shared schema below.&nbsp; This is a 454 period calendar.</P><P>&nbsp;</P><P>I would like a Dynamic column via m-query with below logic:</P><P>1.&nbsp; Label current period = IF [TODAY] "P0" (use [PERIODSTARTDATE] and [PERIODENDDATE])</P><P>2.&nbsp; Based on 1 above, determine "P-3", "P-2", "P-1", "P0", "P+1", "P+2", "P+3" and so forth</P><P>&nbsp;</P><P>The goal here is to aggregate dates into "PERIODLABELS" that represent the planning time horizons of our business.</P><P><BR />To help with context, this is how it would present in Excel:&nbsp;</P><P><img /></P><P>&nbsp;</P><P>&nbsp;</P><P>Help would be greatly appreciated!&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>PERIODENDDATE</TD><TD>FISCALPERIODSORT</TD><TD>FISCALPERIOD</TD><TD>FISCALQUARTER</TD><TD>PERIODSTARTDATE</TD><TD>FISCALQUARTERSORT</TD><TD>FISCALYEAR</TD><TD>DATES</TD><TD>Year</TD><TD>Quarter</TD><TD>Week of year</TD><TD>Start of week</TD><TD>Start of month</TD><TD>CALENDARQTRYEAR</TD><TD>TODAY</TD><TD>Week of fiscal year</TD><TD>FISCALPERIODNUM</TD><TD>Month name</TD><TD>FISCALYEARSHORT</TD><TD>PERIODSHORT</TD><TD>PERIODNAME</TD><TD>This Week</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>27</TD><TD>6/29/2015</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>1</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>27</TD><TD>6/29/2015</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>1</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>27</TD><TD>6/29/2015</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>1</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>27</TD><TD>6/29/2015</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>1</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>27</TD><TD>6/29/2015</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>1</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>28</TD><TD>########</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>2</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>28</TD><TD>########</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>2</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>28</TD><TD>########</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>2</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>28</TD><TD>########</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>2</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>28</TD><TD>########</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>2</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>28</TD><TD>########</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>2</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR><TR><TD>7/31/2015</TD><TD>0</TD><TD>Period 1</TD><TD>Q1</TD><TD>########</TD><TD>0</TD><TD>2016</TD><TD>########</TD><TD>2015</TD><TD>3</TD><TD>28</TD><TD>########</TD><TD>########</TD><TD>2016-Q1</TD><TD>8/18/2024</TD><TD>2</TD><TD>1</TD><TD>Jul</TD><TD>16</TD><TD>P1</TD><TD>P1-Jul</TD><TD>########</TD></TR></TBODY></TABLE> Sun, 18 Aug 2024 01:34:01 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Period-Label/m-p/4105479#M162922 majans 2024-08-18T01:34:01Z Re: Dynamic Period Label https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Period-Label/m-p/4105496#M162923 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="181150" data-lia-user-login="majans" class="lia-mention lia-mention-user">majans</a>&nbsp;,</P> <P>&nbsp;</P> <P>You can generate the desired output by creating sequential numbering for the&nbsp;FISCALYEAR and <SPAN>FISCALPERIODNUM</SPAN> column in your 454 calendar as shown below.</P> <LI-CODE lang="markup">YearMonthSequential = VAR CurrentYear = '454_Calendar'[FISCALYEAR] VAR Current454Month = '454_Calendar'[FISCALPERIODNUM] VAR SequentialNumber = (CurrentYear - MIN('454_Calendar'[FISCALYEAR])) * 12 + Current454Month RETURN SequentialNumber</LI-CODE> <P>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.</P> <P>&nbsp;</P> <P>Best regards,</P> Sun, 18 Aug 2024 02:14:17 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Period-Label/m-p/4105496#M162923 DataNinja777 2024-08-18T02:14:17Z Re: Dynamic Period Label https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Period-Label/m-p/4116564#M163425 <P>Hi DN77,</P><P>&nbsp;</P><P>Apologies for delay.&nbsp;</P><P>This is a viable solution so thank you!</P><P>If I wanted this within my DateDim, how could you achieve same as a custom column?</P><P>&nbsp;</P> Sat, 24 Aug 2024 00:09:02 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Period-Label/m-p/4116564#M163425 majans 2024-08-24T00:09:02Z