Forum Discussion

majans's avatar
majans
Frequent Visitor
2 years ago

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.  ...
DateDim
dates
DataNinja777's avatar
DataNinja777
Super User
2 years ago

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's avatar
    majans
    Frequent Visitor
    2 years ago

    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?