Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Direct Query - Difference between dates

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!

 

 

1 ACCEPTED SOLUTION
I_Like_Pi
Resolver II
Resolver II

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.

View solution in original post

2 REPLIES 2
I_Like_Pi
Resolver II
Resolver II

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
Not applicable

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.