Forum Discussion

PunkDoubtBI's avatar
PunkDoubtBI
Frequent Visitor
7 years ago
Solved

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!
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

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

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    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
     )
)
    • PunkDoubtBI's avatar
      PunkDoubtBI
      Frequent Visitor
      6 years ago

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