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.

Hi Anonymous  - If your data is currently in a wide format (columns for each month), use Power Query > Transform > Unpivot Columns to restructure it into a vertical format with columns like:

Date
Category
Value

create a Date table with continuous dates using DAX

use time intelligence functions like PREVIOUSMONTH, PREVIOUSQUARTER, and YTD calculations.hope this helps.

Hi Anonymous,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to rajendraongole1, FarhanJeelani & danextian for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

Hi Anonymous

It looks like your date values are in a horizontal format (e.g., "Jan,feb,Jul") rather than a proper date format. This is why you're facing difficulty creating a proper chronological axis or applying time intelligence functions in Power BI.

This is how you can resolve this - Unpivot the Data in Power Query

• In Power BI, go to Transform Data to open Power Query.

• Select the month columns (e.g., Jan, Feb, Mar…).

• Right-click on those columns → Unpivot Columns.

• You’ll now have two columns:

  • Attribute (which holds the month name)

  • Value (the corresponding value for that month)

• If needed, rename "Attribute" to "Month" and ensure there's another column for the Year or ID associated with the row (e.g., "2023").

Convert Month Name to Proper Date

You can now create a proper date using DAX or Power Query by combining the Year + Month:

In Power Query:

= #date(Number.FromText([Year]), Date.MonthNameToNumber([Month]), 1)

Or with DAX:

DateFormatted =
DATE(
    VALUE([Year]),
    SWITCH([Month],
        "Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6,
        "Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12
    ),
    1
)

Once that’s done, you’ll have clean, vertically-structured data with a date column perfect for visuals and time intelligence.

Hi Anonymous,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

Hi Anonymous,

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.