How to get value of field T-1 within DIRECT QUERY?

[whu]

Hi,

I want to perform a simple task, but since I'm 'forced' for this project to work with Direct Query, I'm stuck at the moment...

I have a table with in the 1st column month end dates, and in the 2nd the sale amounts. Now I want to show in a visual the sale amounts of the former month.

Normally, I'm using for this DATEADD or PREVIOUSMONTH, see below.

DATEADD = CALCULATE ( [Sales], DATEADD ( Calender[Date] , -1 , MONTH ) )  

PREVIOUSMONTH = CALCULATE ( [Sales], PREVIOUSMONTH ( Calender[Date] ) ) 

However, working within Direct Query, the fields are not recognized, which means (I think) I can't use these ones.

Any suggestions...? Thank you in advance!

[v-yiruan-msft · Accepted solution]

Hi @whu ,

Since you are using a direct query data source, it can't use the date intelligence functions mentioned above. You can create the following measure to get the previous month's data.

Measure =
VAR predate =
    EDATE ( SELECTEDVALUE ( 'Table'[Sale Date] ), -1 )
RETURN
    CALCULATE (
        [Sales],
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Sale Date] = predate )
    )

In addition, you can refer the solution in the following threads to get it.

Previous month column in Direct Query mode

direct query previous month

Best Regards

[VahidDM]

Hi @whu 

You need to add a column to your table for a Former Month:

In Power Query:

1- Sort Ascending your "month-end dates" column in Power Query.

2- Add 2 index columns to your table; the 1st Index starts from 0, and the 2nd start from 1.

3- Merge your table with itself and select those different index columns.

4- Expand Amount column.

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

Appreciate your Kudos  !!