Forum Discussion
Date format in excel
Hi Anonymous
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.
Super UserHi 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.