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 , 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
