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 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=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 thanks for the reply again. the results are showing accurately for each month now, but i noticed it will only show when i filtered it. is there anyway i could have the latest date sum value display on default without applying any filter?
could you also help explain why did my original mesurement didn't work the same way yours did? would help me understand where i did wrong better. thanks!
Hi fksng66, yes we can able to do. Use below formula to get only recent month value. But you have to turn of the interaction for your RecentMonthCount visual with Date slicer. In that way you will achieve it.
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.but isn't that the original one that i used? and i can't get 134,351 for all of 31st may's dates using that fomula for some reason.