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
PunkDoubtBI
Frequent Visitor

Change a calculated column using Earlier () function to using a variable

I've read a blog by Alberto Ferrari  saying that Earlier () should not be used and instead a variable should be created, but I cannot get it to work.

 

Here is the working code using Earlier to find the start date in the next row:

 

StartDateNextStep =
Calculate(Min('Workflow ActivityHistory'[CreatedOn]),
     filter('Workflow ActivityHistory',
              'Workflow ActivityHistory'[DocumentInstanceId] = earlier('Workflow ActivityHistory'[DocumentInstanceId])
       && 'Workflow ActivityHistory'[CreatedOn] > Earlier('Workflow ActivityHistory'[CreatedOn])
      )
)
 
Here is the code I've created to replace the version using Earlier():
 
StartDateNextStep_with_variable =
var CurrentCreatedOn = 'Workflow ActivityHistory'[CreatedOn].[Date]
var CurrentDocumentInstanceId = 'Workflow ActivityHistory'[DocumentInstanceId]

RETURN

Calculate(min('Workflow ActivityHistory'[CreatedOn]),
     filter('Workflow ActivityHistory',
             'Workflow ActivityHistory'[DocumentInstanceId] = CurrentDocumentInstanceId
     && 'Workflow ActivityHistory'[CreatedOn] > CurrentCreatedOn
     )
)
 
The second block of code seems to stop at the current row. It won't return the next row's data.  
Can someone take a look and see why the second block of code won't return the next row's CreatedOn date?
 
Thanks!
1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@PunkDoubtBI 

 

Try deleting the       .[Date] in red font below

 

StartDateNextStep_with_variable =
var CurrentCreatedOn = 'Workflow ActivityHistory'[CreatedOn].[Date]
var CurrentDocumentInstanceId = 'Workflow ActivityHistory'[DocumentInstanceId]

RETURN

Calculate(min('Workflow ActivityHistory'[CreatedOn]),
     filter('Workflow ActivityHistory',
             'Workflow ActivityHistory'[DocumentInstanceId] = CurrentDocumentInstanceId
     && 'Workflow ActivityHistory'[CreatedOn] > CurrentCreatedOn
     )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@PunkDoubtBI 

 

Try deleting the       .[Date] in red font below

 

StartDateNextStep_with_variable =
var CurrentCreatedOn = 'Workflow ActivityHistory'[CreatedOn].[Date]
var CurrentDocumentInstanceId = 'Workflow ActivityHistory'[DocumentInstanceId]

RETURN

Calculate(min('Workflow ActivityHistory'[CreatedOn]),
     filter('Workflow ActivityHistory',
             'Workflow ActivityHistory'[DocumentInstanceId] = CurrentDocumentInstanceId
     && 'Workflow ActivityHistory'[CreatedOn] > CurrentCreatedOn
     )
)

Regards
Zubair

Please try my custom visuals

Removing .[date] worked, thank you. I didn't even notice that intellisense had added this. 

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.

Top Solution Authors