Forum Discussion
Need a leading zero on a Month with DirectQuery
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. :-(
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
12 Replies
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)This is the one that worked for me.
Year-Month = Year([DateColumn]) & "-" & Right("0" & Month([DateColumn]),2)- 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")
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)You should able to do it by using if condition
Year-Month = 'Calendar'[Year] & "-" & if('Calendar'[Month Number] < 10, "0", BLANK()) & 'Calendar'[Month Number]
