Need a leading zero on a Month with DirectQuery

[Jaxidian]

So I have a table that looks like this:

ID int

[Value] decimal(29,9)
DateForSearching datetime
YearNumber int

MonthNumber int

DayNumber int

HourNumber int

I want to do a bunch of visualizations on this data and I need the ability to group them by year-month, year-month-day, and year-month-day-hour. Ideally, those would be represented as (respectively): "2016-12", "2016-12-25", and "2016-12-25 23:00". I can mostly get this done with some new calculated columns (Year-Month = MyTable[YearNumber] & "-" & MyTable[MonthNumber]) except for the leading zero scenarios for something like "2017-01" ends up being "2017-1" (I need the leading zeroes for month, day, and hour).

How can I get these leading zeroes from either my integer values or from the datetime value? I just can't find anything that works with DirectQuery. 😞

[JimKingPowerBI]

I believe that the best solution is the following one that was provided by ebalcaen on 05/31/18.

This is much easier, and will work in all situations:

We add a zero to the month formula by using the concatenation operator (&). Then we use the Right formula to pick only the first two digits from the right.

Year-Month with Trailing Zero on Month:

Year-Month = Year([DateColumn]) & "-" & Right("0" & Month([DateColumn]),2) 

[ebalcaen]

This is much easier, and will work in all situations:

We add a zero to the month formula by using the concatenation operator (&). Then we use the Right formula to pick only the first two digits from the right.

Year-Month with Trailing Zero on Month:

Year-Month = Year([DateColumn]) & "-" & Right("0" & Month([DateColumn]),2) 

[BooDaa]

Thank you @ebalcaen , just what i needed!
Any suggestion on how I can skip it if the the date is not yet set, or should I just filter them out in the visualisation? My result is displayed as "-0" now.

Best regards,
Fredrik

[ebalcaen2]

Looks like I can't login to my old account 😞  But this should work with nulls and as an alternative to the solution below with the IF statement.

Year-Month = Year([DateColumn]) & switch(Month([DateColumn]),1,"-01",2,"-02",3,"-03",4,"-04",5,"-05",6,"-06",7,"-07",8,"-08",9,"-09",10,"-10",11,"-11",12,"-12")

[BooDaa]

Oh yes! IF solved my problem.

dateSolvedYearMonth = 
    IF( ISBLANK(AllRequests[dateSolvedLocalSE]), BLANK(), 
    Year(AllRequests[dateSolvedLocalSE]) & "-" & Right("0" & Month(AllRequests[dateSolvedLocalSE]),2)
   )  

Thank you again!

Best regards,
Fredrik

[fex09]

This is the one that worked for me.

Year-Month = Year([DateColumn]) & "-" & Right("0" & Month([DateColumn]),2) 

[parry2k]

 You should able to do it by using if condition

Year-Month = 'Calendar'[Year] & "-" & if('Calendar'[Month Number] < 10, "0", BLANK()) & 'Calendar'[Month Number] 

[Jaxidian]

It tells me I cannot use an IF in a DirectQuery report.

[parry2k]

I just used the formula on direct query.

What is your data source for direct query? Are you using Live COnnection?

[Jaxidian]

It's direct-to-Azure SQL Database (no caching/syncing).

I thought "DirectQuery" was synonymous with a "live connection". If that's untrue then my terminology is mixed up.

[v-huizhn-msft · Accepted solution]

Hi @Jaxidian,

First, you should click File -> Options and then Settings -> Options -> DirectQuery, then selecting the option "Allow unrestricted measures in DirectQuery mode" shown in following screenshot. When that option is selected, you can create calculated column and measures.



As I tested, If function can be used in DirectQuery Model. I reproduce your scenario(connect to SQL Server database) and get the expected result. Create a column using th following formula, please see the result in screenshot below.

Year-month = IF('HumanResources vEmployeeDepartment'[Month]<=10,CONCATENATE('HumanResources vEmployeeDepartment'[Year],CONCATENATE("-0",'HumanResources vEmployeeDepartment'[Month])),CONCATENATE('HumanResources vEmployeeDepartment'[Year],CONCATENATE("-",'HumanResources vEmployeeDepartment'[Month])))

Please ckeck if you invoke creating calculated columns and measures as the solution above. If you have any question, please let me know.

Best Regards,
Angelia

[Jaxidian]

That solved the problem! And I just deployed the pbix as a Power BI Embedded report and there were no issues with that kind of deployment, either. Thanks for the help!!