Forum Discussion
Date format issue
Hi there, I'am trying to get the last date value of each month, for example I should only be getting sum of all values on 31st May 2025, but the final value output doesn't seem to match with the expected result. I noticed that on my table the date format on the date column is mm/dd/yyyy for date 1-12, but it switched to dd/mm/yyyy starting 13-31
Is there anywhere I can change the date format to be the same? I wonder if it's why I couldn't get the correct expected result.
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
21 Replies
Hi fksng66,
Could you please provide your sample BI report which is having issue. It's easy to understand the issue and will try to provide the solution
here you go, supposedly i should be able to get 134,351 if i take the last date of the month, which in this case is 31st may.
https://drive.google.com/file/d/1nO6ALM70UAUD7GuM9t_4amFceYWdi8cP/view?usp=sharing
Hi fksng66,
The date column(Both Date table and Follower table) is working fine in the attached .pbix file. Please let me know if you have any questions. Here is the snippets.
Here i am attching the .pbix file
https://drive.google.com/file/d/1WJn5FyHLbER_xWl806yaeMhZ3HxU92Q6/view?usp=sharing
Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.
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 AIHi 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
Hiii fksng66
I get what you are trying to say lets solve the issueGo to Date Column -> Transform Data
Use "Using locale" -> as showed in images
alsosee this image and select the preferred Time for you
If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!
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
Hi fksng66 , Thank you for reaching out to the Microsoft Community Forum.
Please refer attached .pbix file for reference and share your thoughts.
If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.hi thanks for the reply, but it's supposed to be the sum of ALL last dates of the month. for example there're multiple rows of 31st may and the result should be the sum of all them.
Hi fksng66 , Thank you for reaching out to the Microsoft Community Forum.
Please refer attached .pbix file for reference and share your thoughts.If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Hi fksng66 , I wanted to check with you and see if the provided information was useful. If any of it helped resolve your question, consider marking it as "Accept as Solution" to make it easier for others to find. Let me know if there's anything else I can assist with!
sorry not really, what im looking for is the sum of ALL rows of lastest dates of previous month, or in this case all rows of 31st may. not the last row of the last date of each month
but i figured my original formula should works fine (posted on one of the reply treads here), but the 05/12/25 and 13/05/2025 different date format is causing the problem that it picks 05/12/2025 as the latest date as it sees it as Dec 5th instead of May 12th
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 , hope you are doing well, Just wanted to know if you have had a chance to look at my previous response and if that helped.
Thank you.
