Forum Discussion

CaveOfWonders's avatar
CaveOfWonders
Helper IV
3 years ago

SQL Lag to DAX measure

Hi All,

 

Could someone please help me recreate the below SQL into a DAX measure.

The SQL is returning the previous Actual Date from the previous ReportingDate Partitioned by the ProjectID and ProjectMilestoneID.

 

 

 

 

SELECT 
  P.ProjectID,
  P.Actual,
  P.ReportingDate,
  P.ProjectMilestoneID,
  LAG(P.Actual) OVER (
    PARTITION BY P.ProjectID, P.ProjectMilestoneID
    ORDER BY P.ReportingDate
  ) AS PreviousActual
FROM Project AS P

 

 

 

 

 Here is the Output in SQL. I've highlighted one example at the bottom where project ID = 181 and Project Milestone ID = 3 it pulls back the previous actual date based on the previous reporting date

 

Sample Table:

ProjectID ActualDate ReportingDate Project Milestone ID
18219-May-2202-Mar-231
18108-Mar-2302-Mar-231
18101-Mar-2323-Feb-231
18219-May-2223-Feb-231
18101-Mar-2316-Feb-231
18219-May-2216-Feb-231
18219-May-2209-Feb-231
18116-Feb-2309-Feb-231
18116-Feb-2302-Feb-231
18219-May-2202-Feb-231
18109-Feb-2326-Jan-231
18219-May-2226-Jan-231
18103-May-2302-Mar-232
18202-Jun-2202-Mar-232
18228-Apr-2223-Feb-232
18106-Mar-2323-Feb-232
18228-Apr-2216-Feb-232
18106-Mar-2316-Feb-232
18121-Feb-2309-Feb-232
18228-Apr-2209-Feb-232
18121-Feb-2302-Feb-232
18228-Apr-2202-Feb-232
18228-Apr-2226-Jan-232
18114-Feb-2326-Jan-232
181 02-Mar-233
182 02-Mar-233
18202-Jun-2223-Feb-233
18129-Mar-2323-Feb-233
18202-Jun-2216-Feb-233
18129-Mar-2316-Feb-233
18202-Jun-2209-Feb-233
18116-Mar-2309-Feb-233
18202-Jun-2202-Feb-233
18116-Mar-2302-Feb-233
18109-Mar-2326-Jan-233
18202-Jun-2226-Jan-233