Forum Discussion

gbarr12345's avatar
gbarr12345
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

Calculated table for date showing incorrect year format

Hi there,

 

I have a calculated table code to create a date table. However, the year is showing as 0204 instead of 2024 and all the year numbers are off.

 

Any idea why this is happening?

 

 

 

CalendarTable =
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "mmmm"),
    "Quarter", FORMAT([Date], "\QQ"),
    "Month Year", FORMAT([Date], "mmm yyyy"),
    "Day", DAY([Date]),
    "Month Number", MONTH([Date]),
    "Week Number", WEEKNUM([Date]),
    "Month Day", FORMAT([Date], "mmmm DD")  // Combined month and day
)
Need Help
  • Shravan133's avatar
    Shravan133
    1 year ago

    Try using CALENDAR(DATE(2022, 1, 1), DATE(2024, 12, 31)) instead of CALENDARAUTO() to limit the date range manually and avoid pulling in unexpected data.

    Also, 

    Check for Corrupt or Incorrect Data: If CALENDARAUTO() is pulling from a dataset that contains incorrectly formatted or invalid date values, these dates could be affecting the output.

    Solution: Inspect the date columns in your model (especially those feeding into the CALENDARAUTO() function) and ensure they contain valid dates with correct year values. You can create a quick report visual in Power BI that shows the minimum and maximum dates in your dataset to check for anomalies:

     

    MinDate = MIN(Table[YourDateColumn]) MaxDate = MAX(Table[YourDateColumn])

5 Replies

  • Shravan133's avatar
    Shravan133
    Icon for Super User rankSuper User
    1 year ago

    Try using CALENDAR(DATE(2022, 1, 1), DATE(2024, 12, 31)) instead of CALENDARAUTO() to limit the date range manually and avoid pulling in unexpected data.

    Also, 

    Check for Corrupt or Incorrect Data: If CALENDARAUTO() is pulling from a dataset that contains incorrectly formatted or invalid date values, these dates could be affecting the output.

    Solution: Inspect the date columns in your model (especially those feeding into the CALENDARAUTO() function) and ensure they contain valid dates with correct year values. You can create a quick report visual in Power BI that shows the minimum and maximum dates in your dataset to check for anomalies:

     

    MinDate = MIN(Table[YourDateColumn]) MaxDate = MAX(Table[YourDateColumn])
    • gbarr12345's avatar
      gbarr12345
      Icon for Post Prodigy rankPost Prodigy
      1 year ago

      Perfect that worked thanks!

       

      Just one more query... I have a table created where the amount numbers aren't pulling in:

       

       

      There should be data as these dates do have numbers in the dataset.

       

      I have created a sample data for it in the images above.

       

      I'm wondering is it a relationship issue?

       

      Each heading is from a different table:

       

       

      Any idea why this is happening?