cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
FlyBoyNight
New Member

Direct Query - 2 Digit Month Insanity

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!

 

 

1 ACCEPTED SOLUTION

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]

or

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

 Personally I would use the first one



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

7 REPLIES 7
melghandour
Regular Visitor

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

alexa_0028
Resolver II
Resolver II

This post really helped me too..Thank you @Stachu 

Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

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 ?

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]

or

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

 Personally I would use the first one



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

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!

glad to help 🙂



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors