cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RMDNA Senior Member
Senior Member

DateTime.LocalNow to number conversion

Hi all,

 

I have a table in my report that contains "DateTime.Date(DateTime.LocalNow())" in order to provide a "last refreshed on:" card. What I'd like to do, for a forecasting piece, is only display events after that date.

 

Our event dates are structured as a whole number in the format "20170323," while DateTime returns a date "3/23/2017," so I need the refresh date as a number in order to filter by "greater than." 

 

When I attempt to convert the date to a whole number data type, it changes to (using the above date) 42817. Any tips?

14 REPLIES 14
RMDNA Senior Member
Senior Member

Re: DateTime.LocalNow to number conversion

bump - anyone?

Super User
Super User

Re: DateTime.LocalNow to number conversion

@RMDNA

 

Hi, in Query Editor with Power Query you can Add a column extracting & concatening your date in the text structure expected.

 

https://msdn.microsoft.com/en-us/library/mt253343.aspx




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




RMDNA Senior Member
Senior Member

Re: DateTime.LocalNow to number conversion

The end goal is to get the date in "whole number" format. I'm able to get the date as text, but I can't change it from there.

 

"3/24/2017" (DateTime) to "20170324" (Whole Number) - it breaks, returns as "42817"

 

"3/24/2017" (DateTime) to "3/24/2017" (Text) - success

              "3/24/2017" (Text) to "20170324" (Whole Number) - "cannot change type"

Super User
Super User

Re: DateTime.LocalNow to number conversion

@RMDNA

 

In Edit Query Add a Custom Column:

 

if Text.Range(Text.From([Date]),2,1)="/" then Text.End(Text.From([Date]),4) & Text.Start(Text.From([Date]),2) & Text.Range(Text.From([Date]),3,2) else Text.End(Text.From([Date]),4) & "0" & Text.Start(Text.From([Date]),1) & Text.Range(Text.From([Date]),2,2)

In my Sample: Date is the Date Field with the Format DD/MM/YYYY

 

DateFormat.png

 

Finally Change the Column Type to Whole Number




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




RMDNA Senior Member
Senior Member

Re: DateTime.LocalNow to number conversion

Fantastic - that turned it into a number.

 

Now, how would I create a page filter of "Table1.[value] is greater than or equal to Table2.[value]?"  The two items are the relationship keys.

Moderator v-yuezhe-msft
Moderator

Re: DateTime.LocalNow to number conversion

Hi @RMDNA,

Create a column using the following DAX, then drag the column to Page level filters and set the value of the column to 1.

Column = IF(Table1[DateColumn]>RELATED(Table2[DateColumn]),1,0)

1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
RMDNA Senior Member
Senior Member

Re: DateTime.LocalNow to number conversion

When creating the column I get the following error:

 

= Table.AddColumn(#"Added Custom", "Name", each if(Table1[CurrentDate]>RELATED("table 2"[StartDate]),1,0))

                                                                                     (Expression.SyntaxError: Token Right Paren Expected   ^ )

Moderator v-yuezhe-msft
Moderator

Re: DateTime.LocalNow to number conversion

Hi @RMDNA,

Right-Click your table in Report View, then select "New column" and apply my DAX code.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RMDNA Senior Member
Senior Member

Re: DateTime.LocalNow to number conversion

Doing so tells me that the expression "related" isn't recognized.

 

= Table.AddColumn(#"Changed Type", "Custom.1",
each if("TABLE 1"[Date1]>RELATED("TABLE 2"[RefreshDate])) then 1 else 0)