Forum Discussion

Kumar_0606's avatar
Kumar_0606
Icon for Helper I rankHelper I
1 year ago
Solved

How to create custom date calculations in Date Table

Hello Experts,   I'm a Beginner and just started creating Data Table in Power BI desktop. In Date table i have created basic columns like Year, Month, Quarter, Week etc. Now i need CM, PM, CQ, PQ...
Need Help
  • Elena_Kalina's avatar
    Elena_Kalina
    1 year ago

    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
        )
)
Elena_Kalina's avatar
Elena_Kalina
Icon for Solution Sage rankSolution Sage
1 year ago

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
        )
)
Kumar_0606's avatar
Kumar_0606
Icon for Helper I rankHelper I
1 year ago

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's avatar
    Elena_Kalina
    Icon for Solution Sage rankSolution Sage
    1 year ago

    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]
)