Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Date format in excel

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 b...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

     

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

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

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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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.