cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 III
Super User III

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 III
Super User III

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

Highlighted
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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors