Direct Query - Difference between dates

[Anonymous]

Hi guys,

First of all, my PBI file is connected with a SQL Database via "Direct Query".

I need to compare ItemLedgerEntry[PostingDate] column with the function "today()", because i need the result wich is the number of days between today and posting date and i just need this number for every row of the table.

The thing is, in "Direct Query" connection seems impossible for me to do that, because I can't create an Aux column in the main table due to the connection.

I've tried it creating a new table and I can create a new measure (Today = Today()) but I don't know the way to add "ItemLedgerEntry[PostingDate]" in the operation.

Example:

Table1, name: ItemLedgerEntry

Column1, name: PostingDate

Column2, name: Sales Amount

What I need is to calculate (remember I'm connected via "Direct Query") the Sales Amount 0 to 30 days before today (Today - Posting Date) and the Sales Amount 30+ days before today.

Thank you!

[I_Like_Pi · Accepted solution]

While you can not create the column in PowerQuery during the import you should be able to make the column by right clicking the table in the main PBI Table listing (on the left) after the data is imported.

Right click the table and then select new Column

New Sales = IF (TODAY()- 'ItemLedgerEntry'[PostingDate]<31,'ItemLedgerEntry'[Sales Amount],0)

Pretty sure this is what you are looking for.

[Anonymous]

Oh, **bleep**. Thank you!

I'm newbie in PowerBI. Didn't realize I can create a new column even if you can't see the values in the main table menu.

Appreciate your help and your quick response!