Date format in excel

[mladird]

Hello, 

I've created only one pbi dashboard so far, and i need to create a new one. 

I've a bit of issue regarding the dates. I have data for each month of 2025. 

For the previous power bi, my dates were put on a "horizontal format" & included in the ID. 

For my new one, i would like to put them in a "vertical format", but i don't know how it would work to do the previous month, previous quarter formula & so on. 

Any tips regarding all of that? Should i keep the format i had initially? 

Thanks

[v-ssriganesh]

Hi @mladird,

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.

[v-ssriganesh]

Hi @mladird,
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.

[rohit1991]

Hi @mladird

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.

[v-ssriganesh]

Hi @mladird,

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.

[rajendraongole1]

Hi @mladird  - 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

DateTable = ADDCOLUMNS (

    CALENDAR ( DATE(2024,1,1), DATE(2025,12,31) ),

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "MMM"),

    "Quarter", "Q" & FORMAT([Date], "Q"),

    "YearMonth", FORMAT([Date], "YYYY-MM"),

    "YearQuarter", FORMAT([Date], "YYYY-Q")

)

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

[mladird]

Thanks I will try that. 

What should i do with the YTD (Year-to-date) columns ? These columns represent the cumulative value from january until the current month? Sometimes it's not just the sum of the months, that's why i can't rely on my own calculations for that (i have to take the number as it is in excel). 

[danextian · Accepted solution]

Hi @mladird 

What do you mean by Sometimes it's not just the sum of the months? If your table has been transformed to a long and narrow format as suggested by @rajendraongole1, and together with a separate date dimensions table, time intelligence calculations should be relatively simple. Please see the attached sample pbix.

[FarhanJeelani]

Hi @mladird ,

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.