topic Re: Date mismatches in DAX Commands and Tips

Date mismatches

Karolina411 — Thu, 03 Mar 2022 23:07:46 GMT

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

Re: Date mismatches

VahidDM — Thu, 03 Mar 2022 23:18:04 GMT

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/

Re: Date mismatches

Anonymous — Thu, 03 Mar 2022 23:19:00 GMT

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

Re: Date mismatches

Karolina411 — Thu, 03 Mar 2022 23:29:56 GMT

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

Re: Date mismatches

Anonymous — Thu, 03 Mar 2022 23:41:24 GMT

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"

Re: Date mismatches

Karolina411 — Fri, 04 Mar 2022 00:16:53 GMT

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

Re: Date mismatches

Anonymous — Fri, 04 Mar 2022 00:21:17 GMT

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 😃

Re: Date mismatches

Karolina411 — Fri, 04 Mar 2022 05:48:36 GMT

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!

Re: Date mismatches

Anonymous — Fri, 04 Mar 2022 06:02:44 GMT

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

Re: Date mismatches

Karolina411 — Fri, 04 Mar 2022 22:44:08 GMT

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!

Re: Date mismatches

Whitewater100 — Sat, 05 Mar 2022 14:19:07 GMT

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])

Re: Date mismatches

Karolina411 — Sat, 05 Mar 2022 17:44:27 GMT

thank you!

Re: Date mismatches

Whitewater100 — Sat, 05 Mar 2022 21:07:14 GMT

Hi:

Can you please mark as solution? Thanks!