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 da...
Need Help
Show and Tell
Tutorial Requests
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
    )
    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Community Support
      4 years ago

      Hi ArchStanton ,

      Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

       

      Best Regards,
      Community Support Team _ kalyj