cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PunkDoubtBI Frequent Visitor
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

Accepted Solutions
Super User
Super User

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

@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
     )
)
Try my new Power BI game Cross the River

View solution in original post

2 REPLIES 2
Super User
Super User

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

@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
     )
)
Try my new Power BI game Cross the River

View solution in original post

PunkDoubtBI Frequent Visitor
Frequent Visitor

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

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,955)