Forum Discussion
Helper IHow to create custom date calculations in Date Table
Hi Kumar_0606
Let me help you create the posting period column and explain the best approach for your relative period requirements.
Single Table Solution
1. Create or Identify Your Date Table
If you don't have a date table yet, create one with this DAX:
DateTable = VAR MinDate = DATE(2020, 1, 1) // Adjust start date as needed VAR MaxDate = DATE(2025, 12, 31) // Adjust end date as needed RETURN ADDCOLUMNS( CALENDAR(MinDate, MaxDate), "DateKey", YEAR([Date]) * 10000 + MONTH([Date]) * 100 + DAY([Date]), "PostingPeriod", YEAR([Date]) * 100 + MONTH([Date]), // Creates YYYYMM format "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"), "Quarter", "Q" & ROUNDUP(MONTH([Date])/3, 0) )
2. Verify Your PostingPeriod Column
The PostingPeriod column will show values like:
202501 for January 2025
202506 for June 2025
202412 for December 2024
Multiple Tables Approach (More Advanced)
1 Create Base Date Table (same as above)
2 Create Relative Period Types Table
Create a separate table with your period types (in Power Query or DAX):
RelativePeriodTypes = DATATABLE( "RelativePeriodType", STRING, { {"CM"}, {"PM"}, {"CQ"}, {"PQ"}, {"CYTD"}, {"PYTD"}, {"PYCM"}, {"PYCQ"}, {"PYSM"}, {"PYSQ"} } )
3Create Combined Table with Relationships
RelativePeriodTable = GENERATE( 'DateTable', 'RelativePeriodTypes', VAR CurrentDate = 'DateTable'[Date] VAR PeriodType = 'RelativePeriodTypes'[RelativePeriodType] VAR RelPeriod = SWITCH( PeriodType, "CM", 'DateTable'[PostingPeriod], "PM", IF('DateTable'[MonthNumber] = 1, ('DateTable'[Year]-1)*100 + 12, 'DateTable'[Year]*100 + 'DateTable'[MonthNumber]-1), "CQ", 'DateTable'[Year]*100 + (ROUNDUP('DateTable'[MonthNumber]/3, 0)-1)*3 +1, // Add other period types similarly BLANK() ) RETURN ROW( "Posting Period", 'DateTable'[PostingPeriod], "Rel Posting Period", RelPeriod, "Relative Period", PeriodType ) )
Helper IApologies for the late response.
I tried to implement the 2nd step but before that I realised I need to create the posting period first. Need help in creating the posting period column logic. The first step mentioned is a table. I would like to clarify if this can be done in a single table or multiple tables.
Solution SageHi Kumar_0606
Let me help you create the posting period column and explain the best approach for your relative period requirements.
Single Table Solution
1. Create or Identify Your Date Table
If you don't have a date table yet, create one with this DAX:
DateTable = VAR MinDate = DATE(2020, 1, 1) // Adjust start date as needed VAR MaxDate = DATE(2025, 12, 31) // Adjust end date as needed RETURN ADDCOLUMNS( CALENDAR(MinDate, MaxDate), "DateKey", YEAR([Date]) * 10000 + MONTH([Date]) * 100 + DAY([Date]), "PostingPeriod", YEAR([Date]) * 100 + MONTH([Date]), // Creates YYYYMM format "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"), "Quarter", "Q" & ROUNDUP(MONTH([Date])/3, 0) )
2. Verify Your PostingPeriod Column
The PostingPeriod column will show values like:
202501 for January 2025
202506 for June 2025
202412 for December 2024
Multiple Tables Approach (More Advanced)
1 Create Base Date Table (same as above)
2 Create Relative Period Types Table
Create a separate table with your period types (in Power Query or DAX):
RelativePeriodTypes = DATATABLE( "RelativePeriodType", STRING, { {"CM"}, {"PM"}, {"CQ"}, {"PQ"}, {"CYTD"}, {"PYTD"}, {"PYCM"}, {"PYCQ"}, {"PYSM"}, {"PYSQ"} } )
3Create Combined Table with Relationships
RelativePeriodTable = GENERATE( 'DateTable', 'RelativePeriodTypes', VAR CurrentDate = 'DateTable'[Date] VAR PeriodType = 'RelativePeriodTypes'[RelativePeriodType] VAR RelPeriod = SWITCH( PeriodType, "CM", 'DateTable'[PostingPeriod], "PM", IF('DateTable'[MonthNumber] = 1, ('DateTable'[Year]-1)*100 + 12, 'DateTable'[Year]*100 + 'DateTable'[MonthNumber]-1), "CQ", 'DateTable'[Year]*100 + (ROUNDUP('DateTable'[MonthNumber]/3, 0)-1)*3 +1, // Add other period types similarly BLANK() ) RETURN ROW( "Posting Period", 'DateTable'[PostingPeriod], "Rel Posting Period", RelPeriod, "Relative Period", PeriodType ) )
- Kumar_0606
Hello Elena,
I created a Date table and RelativePeriodTypes table. When I tried to create the relationship table I don't see the Date field populated
- Elena_Kalina
Sorry. Please, try this one
RelativePeriodTable = SELECTCOLUMNS( GENERATE( CROSSJOIN( 'DateTable', 'RelativePeriodTypes' ), VAR CurrentDate = 'DateTable'[Date] VAR PeriodType = 'RelativePeriodTypes'[RelativePeriodType] VAR CurrentYear = YEAR(CurrentDate) VAR CurrentMonth = MONTH(CurrentDate) VAR CurrentQuarter = ROUNDUP(CurrentMonth/3, 0) VAR RelPeriod = SWITCH( PeriodType, "CM", CurrentYear * 100 + CurrentMonth, "PM", IF(CurrentMonth = 1, (CurrentYear-1)*100 + 12, CurrentYear*100 + CurrentMonth-1), "CQ", CurrentYear * 100 + (CurrentQuarter-1)*3 + 1, "PQ", IF(CurrentQuarter = 1, (CurrentYear-1)*100 + 10, CurrentYear*100 + (CurrentQuarter-2)*3 + 1), "CYTD", CurrentYear * 100 + 1, "PYTD", (CurrentYear-1) * 100 + 1, "PYCM", (CurrentYear-1) * 100 + CurrentMonth, "PYCQ", (CurrentYear-1) * 100 + (CurrentQuarter-1)*3 + 1, "PYSM", (CurrentYear-1) * 100 + IF(CurrentMonth <= 6, 1, 7), "PYSQ", (CurrentYear-1) * 100 + IF(CurrentMonth <= 6, 1, 4), BLANK() ) RETURN ROW( "PostingPeriodValue", CurrentYear * 100 + CurrentMonth, "PeriodTypeValue", PeriodType, "RelativePeriodValue", RelPeriod ) ), "Date", 'DateTable'[Date], "PostingPeriod", [PostingPeriodValue], "RelativePeriodType", [PeriodTypeValue], "RelativePostingPeriod", [RelativePeriodValue] )