Date format in excel

Hi Anonymous ,

Switching to a vertical format (long format) for your date data is generally the best approach in Power BI, as it simplifies DAX calculations and filtering. Here's how you can handle previous month, previous quarter, and other time-based calculations in this format.

1. Creating Time Intelligence Measures
Since your "Period" column contains a mix of monthly (ME01, ME02, etc.) and quarterly (Q1 2025, Q2 2025, etc.) values, it’s best to transform this into a proper Date Table.

A. Convert Periods to Dates
You need to create a proper date column using Power Query or DAX. Ideally, you should transform:

ME01 2025 → 2025-01-01

Q1 2025 → 2025-01-01

Here’s a DAX approach:

DAX

DateColumn =
VAR YearValue = 'Table'[Year]
VAR MonthValue =
SWITCH(TRUE(),
LEFT('Table'[Period],2) = "ME", MID('Table'[Period],3,2),
LEFT('Table'[Period],1) = "Q", VALUE(LEFT(MID('Table'[Period],2,1),1)) * 3 - 2
)
RETURN DATE(YearValue, MonthValue, 1)

This creates a new column with actual date values.

B. Create a Date Table
A dedicated Date Table allows you to use built-in time intelligence functions like PREVIOUSMONTH(), PREVIOUSQUARTER(), etc.

Create a Date Table with:

DAX

DateTable = ADDCOLUMNS(
CALENDAR(DATE(2025,1,1), DATE(2025,12,31)),
"Year", YEAR([Date]),
"MonthNum", MONTH([Date]),
"QuarterNum", QUARTER([Date]),
"MonthYear", FORMAT([Date], "MMM YYYY"),
"QuarterYear", FORMAT([Date], "\QQ YYYY")
)

Then, create a relationship between your transformed DateColumn and this Date Table.

C. Previous Month & Quarter Calculations
Now, with a proper Date Table, you can create simple measures:

Previous Month Sales

DAX

PreviousMonthSales =
CALCULATE( SUM('Table'[Sales]), PREVIOUSMONTH('DateTable'[Date]) )

Previous Quarter Sales

DAX

PreviousQuarterSales =
CALCULATE( SUM('Table'[Sales]), PREVIOUSQUARTER('DateTable'[Date]) )

Year-over-Year Growth

DAX

YoYGrowth =
VAR PreviousYearSales = CALCULATE(SUM('Table'[Sales]), SAMEPERIODLASTYEAR('DateTable'[Date]))
RETURN DIVIDE(SUM('Table'[Sales]) - PreviousYearSales, PreviousYearSales)

Please mark this post as solution if it helps you. Appreciate Kudos.