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
Highlighted
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
     )
)

View solution in original post

2 REPLIES 2
Highlighted
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
     )
)

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 467 members 4,069 guests
Please welcome our newest community members: