Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
4 years ago

Previous Date Offset - DAX Calculated Column

Hi,

I have been using the link below to hopefully fix an issue where I need to calculate the DAYSBETWEEN the ActualStartDate & ActualEndDate using a calculated column. 

 

My problem is when the dates are on different rows:

 

 

IF Column D (OwnerID) is a duplicate then I need to Calculate the DAYSbetween A2,B2 on 1st Row then C2,B3 on the next row (I have colour coded this to make it easier to read).

 

Row 4 is a single record so only has one row and is straightforward.

 

Is something like this even possible?

 

Thanks

 

 

 

Need Help
Show and Tell
Tutorial Requests

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    ArchStanton ,

    New column =

    var _1 = maxx(filter(Table, [owner id] = earlier([owner id]) && [Actual end] < earlier([Actual end]) ),[Actual end])

    return

    if(isblank(_1), networkdays([Case open], [actual start],1) , networkdays(_1 , [actual end],1) )

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      4 years ago

      Thanks for your reply, I'm trying your way in a separate column but get the following error message:

       

      The syntax for 'return' is incorrect. (DAX(maxx(filter(Deferrals, [regardingobjectid] = earlier([regardingobjectid]) && [Actual end] < earlier([Actual end]) ),[Actual end])returnif(isblank(_1), networkdays([Case Received], [actual start],1) , networkdays(_1 , [actual end],1) ))).

       

      I'm not using NETWORKDAYS, instead I have a Date Table that I have already used to calculate how long its been since Case opened (aka Case Received) and ActualEndDate

       

       

      Time in Deferral AP = CALCULATE(
    COUNTROWS('Date Dimension'),
    DATESBETWEEN('Date Dimension'[Date],Deferrals[actualstart],Deferrals[actualend]),'Date Dimension'[Is Working Day] = TRUE(),
    ALL('Date Dimension')
)

       

       (see above) 

       

      Can this be incorporated into your Code do you think? 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

    Expected result CC =
VAR _countrowsID =
    COUNTROWS ( FILTER ( Data, Data[OwnerID] = EARLIER ( Data[OwnerID] ) ) )
VAR _numberingrowsID =
    COUNTROWS (
        FILTER (
            Data,
            Data[OwnerID] = EARLIER ( Data[OwnerID] )
                && Data[ActualStart] <= EARLIER ( Data[ActualStart] )
        )
    )
VAR _networkdaysAB =
    NETWORKDAYS ( Data[Case Opened], Data[ActualStart] )
VAR _previousrowactualend =
    MAXX (
        FILTER (
            Data,
            Data[OwnerID] = EARLIER ( Data[OwnerID] )
                && Data[ActualStart] < EARLIER ( Data[ActualStart] )
        ),
        Data[ActualEnd]
    )
VAR _networkdaysCB =
    NETWORKDAYS ( _previousrowactualend, Data[ActualStart] )
VAR _networkdaysAC =
    NETWORKDAYS ( Data[Case Opened], Data[ActualEnd] )
RETURN
    SWITCH (
        TRUE (),
        _countrowsID = 2, IF ( _numberingrowsID = 1, _networkdaysAB, _networkdaysCB ),
        _networkdaysAC
    )
    • ArchStanton's avatar
      ArchStanton
      Power Participant
      4 years ago

      Thanks for this, it almost works but not quite.

      I'm not actually using NETWORKDAYS as that was just an example to explain how the DAYS DIFFERENCE was achieved in a simple way, instead, I am using a better calculation which ignores weekends & public holidays which is below:

       

      Time in Deferral AP = CALCULATE(

          COUNTROWS('Date Dimension'),
          DATESBETWEEN('Date Dimension'[Date],Deferrals[actualstart],Deferrals[actualend]),'Date Dimension'[Is Working Day] = TRUE(),
          ALL('Date Dimension')
      )
       
      Could I replace your Variables _networkdaysAB, CB & AC with that code but tweaking it like you have?
       
      My code reads like this at the moment:
       
       
      • ArchStanton's avatar
        ArchStanton
        Power Participant
        4 years ago
        Pseudo Days = 
VAR _countrowsID =
    COUNTROWS ( FILTER ( Deferrals, Deferrals[regardingobjectid] = EARLIER ( Deferrals[Regardingobjectid] ) ) )
VAR _numberingrowsID =
    COUNTROWS (
        FILTER (
            Deferrals,
            Deferrals[Regardingobjectid] = EARLIER ( Deferrals[Regardingobjectid] )
                && Deferrals[ActualStart] <= EARLIER ( Deferrals[ActualStart] )
        )
    )
VAR _networkdaysAB =
    NETWORKDAYS ( Deferrals[Case Received], Deferrals[ActualStart] )
VAR _previousrowactualend =
    MAXX (
        FILTER (
            Deferrals,
            Deferrals[Regardingobjectid] = EARLIER ( Deferrals[Regardingobjectid] )
                && Deferrals[ActualStart] < EARLIER ( Deferrals[ActualStart] )
        ),
        Deferrals[ActualEnd]
    )
VAR _networkdaysCB =
    NETWORKDAYS ( _previousrowactualend, Deferrals[ActualStart] )
VAR _networkdaysAC =
    NETWORKDAYS ( Deferrals[Case Received], Deferrals[ActualEnd] )
RETURN
    SWITCH (
        TRUE (),
        _countrowsID = 2, IF ( _numberingrowsID = 1, _networkdaysAB, _networkdaysCB ),
        _networkdaysAC
    )