Forum Discussion
Date format issue
Hi fksng66 , Thank you for reaching out to the Microsoft Community Forum.
Unfortunately, what you're trying to do isn't possible with DAX or measures alone while the date column has inconsistent formats like mm/dd/yyyy and dd/mm/yyyy. Power BI cannot reliably detect the correct last date of the month if it's misinterpreting some dates (like 05/12/2025 being seen as December 5 instead of May 12).
The only way to make your original formula work is to ensure the column is a proper Date type with a consistent format during the data load stage ideally in Power Query using the Change Type Using Locale option. Otherwise, any logic based on date comparisons will return incorrect results.
I know it's not the answer you'd hoped for but just wanted to clarify that this limitation isn't something DAX or visuals can fix it has to be resolved at the source or Power Query level.
Custom Date Formats in Power BI Using a Slicer - Microsoft Fabric Community
Hi fksng66 ,
This is a common issue in Power BI (and Excel) when date formats are inconsistent or misinterpreted. What’s happening is likely due to Power BI (or your data source) reading the date column as text, which causes dates like 1/12/2025 to be interpreted as either January 12th or December 1st, depending on locale settings. When values exceed 12 for the day part, it switches interpretation (since there’s no 13th month).
How to Fix:
Ensure Your Column is a Date Type:
- In Power Query, select the Date column.
- Set the data type to “Date” (not text).
- If it prompts for a locale, choose the correct one (e.g., “English (United States)” for mm/dd/yyyy or “English (United Kingdom)” for dd/mm/yyyy).
Explicitly Set Locale (in Power Query):
- Right-click the column > Change Type > Using Locale…
- Pick Data Type: Date, and then select your desired locale.
- This forces Power Query to interpret all dates with the same format.
Check Source Data:
- If the source file (CSV, Excel, etc.) mixes formats, clean it up so all dates are in the same format before loading to Power BI, if possible.
After Conversion:
- Once the column is correctly converted to date type, Power BI will internally handle all dates the same way, regardless of display format.
Summary:
Yes, inconsistent date formats can break your calculations, especially when trying to get the last value per month. Fixing the data type (and locale) will resolve this and ensure you get correct results.
References:
If you follow these steps and re-load your data, your measure to get the last date value per month should work as expected.
Let me know if you need a step-by-step or have more questions!
translation and formatting supported by AI
Hi thanks for the reply, I've tried to change it on both power query and also source file but doesn't seems to make all into the same dd/mm/yyyy format still
