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,
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 but how could i show it into these format with this measurement? supposedly the measurement should pickup the latest value of previous month
HI fksng66 ,
You can use below modifued measure to get selected month last date follower value.
LastDateSum_Audience = Var SelectedDate = MAX(DateTable[Date]) VAR LastDateOfMonth = CALCULATE(EOMONTH(SelectedDate,0), //finds the last date for selected month ALLEXCEPT('Follower', 'Follower'[Month])) //ensures that the calculation happens within each month Var Result = CALCULATE(SUM('Follower'[Audience]), 'Follower'[Date] = LastDateOfMonth) //sums only the rows where the date matches the last date of that month RETURN Result
https://drive.google.com/file/d/1e1jYR7mTvQW_AbAOAICY9ic_Q79helb8/view?usp=sharingThanks,
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.