Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
Best Regards
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
Best Regards
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 !!