topic Re: Direct Query - 2 Digit Month Insanity in DAX Commands and Tips

Direct Query - 2 Digit Month Insanity

FlyBoyNight — Fri, 24 Apr 2020 15:17:17 GMT

Hi everyone,

Really new to Power BI and Dax, so thanks in advance!

I'm trying to get 2 digit month using Dax and Direct Query. Ultimately, I am trying to order data visuals correctly, using YYYYMM as my date. I've got the year part figured out and in a column already using YEAR function, which I could concatenate with my new Month columnm, but as lots of people know Dax MONTH function only retrurns single digit Month, e.g. 4 vs 04. In order to correctly order my time data, I would need YYYYMM.

I've looked long and hard and almost every solution out there involves Dax functions that are not supported in Direct Query mode. I've tried FORMAT, IF statements, countless others.

My source column has 'date' data with Date, Month, Year, and Time values in it. The column is already formatted as that in Power BI

Thanks!

Re: Direct Query - 2 Digit Month Insanity

Stachu — Fri, 24 Apr 2020 18:48:21 GMT

how about something like this:

YearMonth = YEAR(Calendar[Date])*100 + MONTH(Calendar[Date])

it will give you YYYYMM as numeric value, but I don't think it should be an issue

Re: Direct Query - 2 Digit Month Insanity

FlyBoyNight — Fri, 24 Apr 2020 21:27:54 GMT

Here is a link to a screen shot.

https://www.dropbox.com/s/6o9gjfgmn18i2sh/2020-04-24_16-21-22.png?dl=0

My original column is DateofService which I broke out into DateofServce Year and separately Month. Finally your column is DateofServiceYearMonth.

DateofServiceYearMonth = YEAR(RequestSummary[DateofServiceYear])*100 + MONTH(RequestSummary[DateofServiceMonth])

I am getting random numbers it appears ?

Re: Direct Query - 2 Digit Month Insanity

Stachu — Fri, 24 Apr 2020 21:53:32 GMT

YEAR and MONTH take Date as an argument, with your syntax it's just the number of days starting from 1899-12-31, where 2019 gives year 1905 and 4 gives 1 (January 1900)

You can use either

DateofServiceYearMonth = RequestSummary[DateofServiceYear]*100 + RequestSummary[DateofServiceMonth]

DateofServiceYearMonth = YEAR(RequestSummary[DateofService])*100 + MONTH(RequestSummary[DateofService])

Personally I would use the first one

Re: Direct Query - 2 Digit Month Insanity

FlyBoyNight — Fri, 24 Apr 2020 22:18:25 GMT

Stachu,

Thank you so very, very much. I can't tell you how many hours I spent trying different iterations of other options. This is elegant and perfect for what I need.

Cheers!

Re: Direct Query - 2 Digit Month Insanity

Stachu — Sat, 25 Apr 2020 09:25:09 GMT

glad to help 🙂

Re: Direct Query - 2 Digit Month Insanity

alexa_0028 — Wed, 02 Mar 2022 15:27:10 GMT

This post really helped me too..Thank you Stachu

Re: Direct Query - 2 Digit Month Insanity

Anonymous — Tue, 16 May 2023 07:51:44 GMT

you can use the function Number.ToText as below
Number.ToText([MonthNumber],"D2")

Re: Direct Query - 2 Digit Month Insanity

Mehranmus — Wed, 17 Jul 2024 01:10:47 GMT

worked, amazing