DateTime.LocalNow to number conversion

[RMDNA]

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?

[RMDNA]

bump - anyone?

[Vvelarde]

@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

[RMDNA]

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]

@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

Finally Change the Column Type to Whole Number

[RushiS]

Thank you for this M query. You saved my time

[RMDNA]

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.

[v-yuezhe-msft]

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)

Thanks,
Lydia Zhang

[RMDNA]

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

[v-yuezhe-msft]

Hi @RMDNA,

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

Thanks,
Lydia Zhang

[RMDNA]

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)

[v-yuezhe-msft]

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.



Thanks,
Lydia Zhang

[RMDNA]

@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:

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?

[v-yuezhe-msft]

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

[RMDNA]

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

[v-yuezhe-msft]

Hi @RMDNA,

Could you please share me sample data of your tables?

Thanks,
Lydia Zhang