How to create custom date calculations in Date Table

[Kumar_0606]

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, CYTD, PYTD, PYCM, PYCQ, PYSM and PYSQ in a single column like below along with Rel Posting period column.

Posting Period     Rel Posting Period     Relative Period

---------------------------------------------------------------

202506                202506                      CM

202506                202505                      PM

202506                202506                      CQ

202506                202503                      PQ

202506                202506                      CYTD

202506                202406                      PYTD

202506                202406                      PYCM

202506                202406                      PYCQ

202506                202406                      PYSM

202506                202406                      PYSQ

Any help would be greatly appreciated.

Thanks

Kumar

[v-nmadadi-msft]

Hi @Kumar_0606 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Kumar_0606]

Apologies 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.

[Elena_Kalina · Accepted solution]

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]

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

[Elena_Kalina]

Hi @Kumar_0606 

1. Create a Relative Periods Reference Table

First, create a small table with all your period types:

// In Power Query Editor (Home > Transform data)
let
    Source = #table(
        {"RelativePeriodType"}, 
        {
            {"CM"},  // Current Month
            {"PM"},  // Previous Month
            {"CQ"},  // Current Quarter
            {"PQ"},  // Previous Quarter
            {"CYTD"}, // Current Year-to-Date
            {"PYTD"}, // Previous Year-to-Date
            {"PYCM"}, // Previous Year Current Month
            {"PYCQ"}, // Previous Year Current Quarter
            {"PYSM"}, // Previous Year Same Month
            {"PYSQ"}  // Previous Year Same Quarter
        }
    ),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RelativePeriodType", type text}})
in
    #"Changed Type"

2. Create a Function to Calculate Relative Periods

Now create a function that calculates the relative posting period for any given date:

// Create a new blank query
(PostingPeriod as number, RelativePeriodType as text) as number =>
let
    CurrentDate = Date.From(Text.From(PostingPeriod), // Convert YYYYMM to date
    Year = Date.Year(CurrentDate),
    Month = Date.Month(CurrentDate),
    
    RelativePeriod = 
        if RelativePeriodType = "CM" then PostingPeriod
        else if RelativePeriodType = "PM" then 
            if Month = 1 then (Year-1)*100 + 12
            else Year*100 + (Month-1)
        else if RelativePeriodType = "CQ" then PostingPeriod // Current quarter same as current month
        else if RelativePeriodType = "PQ" then
            if Month <= 3 then (Year-1)*100 + (12 - (3 - Month))
            else Year*100 + (Month - 3)
        else if RelativePeriodType = "CYTD" then PostingPeriod // Current YTD same as current month
        else if RelativePeriodType = "PYTD" then (Year-1)*100 + Month
        else if RelativePeriodType = "PYCM" then (Year-1)*100 + Month
        else if RelativePeriodType = "PYCQ" then (Year-1)*100 + Month
        else if RelativePeriodType = "PYSM" then (Year-1)*100 + Month
        else if RelativePeriodType = "PYSQ" then (Year-1)*100 + Month
        else null
in
    RelativePeriod

3. Create the Final Table Using DAX

Now create a calculated table in your model:

RelativePeriodTable = 
GENERATE(
    'DateTable',  // Your base date table
    'RelativePeriods',  // The table you created in Step 1
    VAR PostingPeriod = 'DateTable'[PostingPeriod]  // Your YYYYMM column
    VAR PeriodType = 'RelativePeriods'[RelativePeriodType]
    VAR RelPostingPeriod = [YourFunctionName](PostingPeriod, PeriodType)  // Call the function you created
    RETURN
        ROW(
            "Posting Period", PostingPeriod,
            "Rel Posting Period", RelPostingPeriod,
            "Relative Period", PeriodType
        )
)

4. Format the Output Table

1. Ensure "Posting Period" and "Rel Posting Period" are formatted as whole numbers

2. Create relationships between this new table and your fact tables

Alternative Beginner-Friendly Approach

If the above seems complex, you can create these directly in your date table with calculated columns:

// In your date table
Relative Period = "CM"  // Create column with default value

// Then create a calculation for Rel Posting Period
Rel Posting Period = 
SWITCH(
    [Relative Period],
    "CM", [PostingPeriod],
    "PM", IF([MonthNumber] = 1, 
            ([Year] - 1) * 100 + 12, 
            [Year] * 100 + [MonthNumber] - 1),
    "CQ", [PostingPeriod],
    "PQ", IF([MonthNumber] <= 3, 
            ([Year] - 1) * 100 + 9 + [MonthNumber], 
            [Year] * 100 + [MonthNumber] - 3),
    // Continue with other cases
    [PostingPeriod]  // Default case
)

[danextian]

What's the purpose of creating those rel posting period values?

Wouldn't it be more practical to use calculation groups that would apply time intelligence calculations relative to the currently selected period in the slicer or in a visual?

[burakkaragoz]

Hi @Kumar_0606 ,

You can create these “Relative Period” columns in Power BI using calculated columns in your Date Table. Here’s a simple approach for each of the ones you listed, using DAX:

Example for “CM” (Current Month) and “PM” (Previous Month):

dax

CM = IF('Date'[Posting Period] = 'Date'[Rel Posting Period], "CM")
PM = IF('Date'[Posting Period] = EDATE('Date'[Rel Posting Period], 1), "PM")

But since you want all these relative periods in a single column, you can use a SWITCH statement to create a “Relative Period” calculated column:

dax

Relative Period = SWITCH(
    TRUE(),
    'Date'[Posting Period] = 'Date'[Rel Posting Period], "CM",
    'Date'[Posting Period] = EDATE('Date'[Rel Posting Period], 1), "PM",
    'Date'[Posting Period] = 'Date'[Rel Posting Period] && 'Date'[Quarter] = 'Date'[Quarter], "CQ",
    'Date'[Posting Period] = 'Date'[Rel Posting Period] - 3, "PQ",
    -- Add more logic for CYTD, PYTD, etc. depending on your business rules
    BLANK()
)

You’ll need to expand the SWITCH to cover all periods like CYTD, PYTD, PYCQ, etc. For each, determine the logic (e.g., compare year-to-date, prior year-to-date, prior year same month, etc.). Functions like SAMEPERIODLASTYEAR, PARALLELPERIOD, and DATESYTD will help.

If you’re new to DAX, start by building a few columns one by one to check the logic, then combine into the big SWITCH formula.

Let me know which specific relative periods you need help with and I can give you the DAX for each!
translation and formatting supported by AI

[Kumar_0606]

Hello Burakkaragoz,

Thanks for the DAX formula for each calculations.

However i need the Rel Posting period calculation as well based on the Posting period.