Helper 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

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

burakkaragoz

Super User

1 year ago

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

Helper I

1 year ago

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.

Forum Discussion

How to create custom date calculations in Date Table

Single Table Solution

1. Create or Identify Your Date Table

2. Verify Your PostingPeriod Column

Multiple Tables Approach (More Advanced)

1 Create Base Date Table (same as above)

2 Create Relative Period Types Table

3Create Combined Table with Relationships