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

[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