Date column is not being converted properly while importing from an excel

[Avivek]

I am facing 2 issues:

1. Firstly, when I am connecting the power bi model to an excel file, the date column is showing issues. The date in the excel is text format and when bought in power bi it takes month as day and day as month. Even when i change the date format, it doesn't help much.

So if you see in the above image, the same posting date if taken as a hierarchy shows day as month and month as day. How can we fix this without doing any transformation in the excel.

2. Secondly, because of this issue i am unable to calculate my current week sales. How do we calculate current week sales?

[Anonymous · Accepted solution]

Hi @Avivek ,

The date format is related with your location you set up. You can use "Using Locale..." to fix it.

Below is an example, my default format for dates is MM/DD/YYYY. So DD/MM/YYYY dates are imported in and text is displayed.

If your DD does not exceed 12 days, then the import may detect the date as MM/DD/YYYY, i.e. the month and days are reversed.

For the column you want to reformat the date, right-click and select Using Locale...

Select the Data Type as Date and Locale as the location you want. Here I selected English( United Kingdom) which date format is DD/MM/YYYY. You can see the sample input values for reference.

Note that before using Using Locale, restore the date to text format before working with it.

For calcualting the current sales, you can try creating the following measure.

Current Week Sales = CALCULATE(SUM('Table (2)'[Value]), DATESBETWEEN('Table (2)'[Date], TODAY() - WEEKDAY(TODAY(), 2) + 1, TODAY()))

If you want to return the total for all rows in the table visual. You can modify as

Current Week Sales = CALCULATE(SUM('Table (2)'[Value]), DATESBETWEEN('Table (2)'[Date], TODAY() - WEEKDAY(TODAY(), 2) + 1, TODAY()),ALLSELECTED('Table (2)'))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.