Date format issue

[fksng66]

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.

[v-hashadapu]

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.

[v-hashadapu]

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!

[fksng66]

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 

[v-hashadapu]

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

[v-hashadapu]

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.

[fksng66]

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.

[v-hashadapu]

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.

[ajaybabuinturi]

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 

[fksng66]

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 

[ajaybabuinturi]

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.

[fksng66]

hi but how could i show it into these format with this measurement? supposedly the measurement should pickup the latest value of previous month

[ajaybabuinturi]

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.

[fksng66]

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!

[ajaybabuinturi]

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.

[fksng66]

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.

[ajaybabuinturi]

@fksng66 the original DAX is works on recent/latest date, in your data 12/5/2025 is the recent/Latest date. That is the reason you can see directly May-25. If you want to see selected month data use provided measure.

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.

[fksng66]

How did you see 12th may is the latest date tho? cause it clearly shows there's 31st may on my side

[Khushidesai0109]

Hiii @fksng66


I get what you are trying to say lets solve the issue

Go to Date Column -> Transform Data

Use "Using locale" -> as showed in images

also 

see 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!
 

[fksng66]

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

[burakkaragoz]

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:

1. 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).

2. 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.

3. 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.

4. 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:

• Power Query: Changing column type using locale
• Dealing with date formats in Power BI

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