Date mismatches

[Karolina411]

Hello

So...my date column in my date dimension is not working all of a sudden----I tried to ensure both are dates only but that is not working - when I use MONTHDATE (as below) it cannot get into date order.  Surely this is silly .....any suggestions ??  I just need a simple solution on how to display month/year by the month name/yr and suddently my date table seems to be a string value

[Whitewater100]

Hi:

CALC COL for Date Table. This will allow the sorting you want but also is very helpful when you are trying to zone in on particular months, or groups of months. Lot's of applications!

Running Month Index =

VAR minyear = YEAR(MIN(Dates[Date]))

VAR thisyear = YEAR(Dates[Date])

Return

(thisyear - minyear) * 12 + MONTH(Dates[Date])

[Karolina411]

thank you!

[Whitewater100]

Hi:

Can you please mark as solution? Thanks!

[Anonymous]

Hi @Karolina411,

Try the solution in this link, add a new column and sort the Month column using the new column.

https://community.powerbi.com/t5/Desktop/not-able-to-sort-by-months-in-chronological-order/m-p/61035

[Karolina411]

I can hardly understand how he wrote those complex instructions....how can there be  a program that does not sort dates in chronological order?

[Anonymous]

Right Click "DIMDATE", Add a new column, and type in the following code:

yearMonth = RIGHT(YEAR('DIMDATE'[DateValue]),2) &

SWITCH(MONTH('DIMDATE'[DateValue]),

1,"01",

2,"02",

3,"03",

4,"04",

5,"05",

6,"06",

7,"07",

8,"08",

9,"09",

10,"10",

11,"11",

12,"12"

)

Then Left Click the "FormattedMonthYear" under "DIMDATE" and sort the column by "yearMonth"

[Karolina411]

Thank you ..I will try this later today...appreciate the clarity

[Anonymous]

No problem, I'm new to Power BI as well and I faced the same problem a few days ago. That was the way I solved it. Hope it can solve yours as well 😃

[Karolina411]

I would not say I am a total neophyte but how can a Microsoft write such a software that cannot interpret chronological dates..I guess that is life!

thanks again!

[Anonymous]

I think it is because when you format the Date into string, it will not see it as a Date anymore so it sort the Formatted Date according to the ASCII code from A to Z

[Karolina411]

@Anonymous I know...it happens sometimes w/ my Date Dimensions out of nowhere....I suspect it is my relationships running in the bg.  I got your code to work!  thank you as you save me alot of frustration! 

[VahidDM]

Hi @Karolina411 

Add a calculated column based on your date to show the YYYYMM like 202101=Jan-21

then create another column to show the MMMM YY like January 2021, and sort it by the previous ↑ calculated column.

Then you can use it in your report.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/