cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RMDNA
Solution Sage
Solution Sage

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?

15 REPLIES 15
RMDNA
Solution Sage
Solution Sage

bump - anyone?

Vvelarde
Community Champion
Community Champion

@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

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"

Vvelarde
Community Champion
Community Champion

@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
RushiS
Frequent Visitor

Thank you for this M query. You saved my time

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.

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.

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   ^ )

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.

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)

Hi @RMDNA,

Do not create the column in Query Editor. Right-Click your table in Report View, then select "New column" and apply my DAX code.
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.

@v-yuezhe-msft

 

Sorry - it broke again. Here's your code:

 

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

 

Here's a picture of my "add column" from right-clicking in the standard report view and pasting the DAX, in case I'm doing anything wrong:

 

1.PNG

 

I get the error:

"A single value for column 'StartDateID' in table 'Table One' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

To reiterate my goal, each entry in "Table One" is unique, and has a whole number "StartDateID" of when the event was run. I've successfully changed localtime into a whole number in Table Two, and I'm attempting to say "only display Events (in table one) where the event date is (greater/less than) the current date (out of table two).

 

Additionally, from what you mentioned earlier: what's the functional difference between doing "add column" in report view and adding a column in Edit Queries?

Hi @RMDNA,

Do you choose "New Measure" or "New column" to apply my DAX? Please ensure that you choose "New column" as highlighted in my screenshot. Also make sure that you have created relationship between the two tables.

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.

@v-yuezhe-msft- Yes, I already had the many:1 relationship, and chose "New Column."

Hi @RMDNA,

Could you please share me sample data of your tables?

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.