Identify non duplicates, to remove, and then calculate time spend on two steps, and average as well

Power BI Data Model
41

That would be amazingly helpful, Brian. I tried the link, but got this message: " Oops! That page doesn’t exist or is private." If you do have a second, could help implement, or higher level step by step, or I could work it out from pbix if you would create it in the example. By the way, your dax worked perfectly with a couple tweaks. was so pleased!!

42

@Jetuke_ES,

I’ve been getting that message too when I click on forum links that others have sent to me. Thought it might be a problem at my end, but it looks like it’s a more widespread issue. I will alert @sam.mckay and the @EnterpriseDNA team.

The search function is still working though, so just click on the magnifying glass at the top right of the screen, enter “networkdays” and you should see the following post at the top of the list:

image
image671×548 35.2 KB

Click on that, and it will bring you to the instructions and the M code for the NETWORKDAYS function.

After you implement the function, take a look at your date table in the Data View and you will see exactly how Imke implemented this, and how incredibly powerful and straightforward it now makes this sort of calculation. Sort of inexplicable that Microsoft has not included NETWORKDAYS as a functioning DAX, given that it has existed in Excel for years.

  • Brian
44

i tried this one to calculate duration with excluding weekends. created Date table, also custom column for Workdays. And tried this dax with couple variations, but dont seem to make it work…

Closure Time =
CALCULATE(SUM(Dates[IsWorkdayDay]),
DATESBETWEEN(Dates[Date], Table1[Min Created On], Table1[Max Completed On]-1),
Dates[IsWorkdayDay] = TRUE,
ALL(Table1[Overall Process])
)

any tips would be really appreciated, please…

45

@Jetuke_ES,

Going back through this thread, I’ve lost track of what the most current version of the PBIX we are working with is. If you could please post that, I’d be glad to work through the NETWORKDAYS calculation.

Thanks.

  • Brian
46

I can see why it would be confusing. So i made this version work and it didn’t increase too massively when working with the actual file. I created Date table with “IsWorkingDay” - True/False, in hopes to somehow be able to exclude False/Weekends from the “Elapsed Days” and “Average” calculations. Thank you Brian for any help.
eDNA Forum - Process Duplicates Solution.pbix (49.9 KB)

47

@Jetuke_ES,

OK, I think I got it working, and it’s an interesting scenario for the use of the NETWORKDAYS function, which I first applied to the Dates table. Here’s the measure that does the heavy lifting. Basically, it computes the fractional days on the min and max ends (zeroing out one or both if they fall on weekends) and then adding those two fractional days to the full net work days in between:

Total NetWorkDays = 

VAR FirstDay = [Min Created On]
VAR YrMin = YEAR( FirstDay )
VAR MoMin = MONTH( FirstDay )
VAR DyMin = DAY( FirstDay )
VAR FirstDayWeekend = 
    SWITCH( TRUE(),
        WEEKDAY( FirstDay ) = 7, 0,
        WEEKDAY( FirstDay ) = 1, 0,
        1
    )

VAR LastDay = [Max Completed On]
VAR YrMax = YEAR( LastDay )
VAR MoMax = MONTH( LastDay )
VAR DyMax = DAY( LastDay )
VAR LastDayWeekend = 
    SWITCH( TRUE(),
        WEEKDAY( LastDay ) = 7, 0,
        WEEKDAY( LastDay ) = 1, 0,
        1
    )

VAR FirstDayEnd =
    DATE( YrMin, MoMin, DyMin ) + TIME(23, 59, 59 )

VAR LastDayBegin =
    DATE( YrMax, MoMax, DyMax ) + TIME( 0, 0, 0 )

VAR SecondsPerDay = 24 * 60 * 60

VAR FirstDayDays =
    DIVIDE(
        DATEDIFF(
            FirstDay,
            FirstDayEnd,
            SECOND
        ),
        SecondsPerDay,
        0
    )

VAR LastDayDays =
    DIVIDE(
        DATEDIFF(
            LastDayBegin,
            LastDay,
            SECOND
        ),
        SecondsPerDay,
        0
    )

VAR NetWorkDaysDiff = 
[Networkdays Created On] - [Networkdays Completed On]

VAR FullNetWorkDays = 
IF(
    NetWorkDaysDiff < 2,
    0,
    NetWorkDaysDiff - 1
)

RETURN
(FirstDayDays * FirstDayWeekend) + FullNetWorkDays + (LastDayDays * LastDayWeekend)

Then computing the average is a straightforward matter of substituting the measure above into your prior AVERAGEX calculation:

Average NetWorkDays Elapsed = 

VAR vTable =
    FILTER(
        Data,
        Data[Keep Record] = "Yes"
    )

VAR vTable2 =
    ADDCOLUMNS(
        SUMMARIZE(
            vTable,
            Data[Overall Process]
        ),
    "Total NWD", [Total NetWorkDays]
    )

RETURN
AVERAGEX(
    vTable2,
    [Total NWD]
)

image
image840×252 13.5 KB

I hope this is helpful. Full solution file attached below.

1 Like
48

To complement another excellent post by @BrianJ

Here’s a Power Query approach using a custom function made by Bill Szysz, for more details on how to implement that see this thread. Time format 37:30:55 greater than 24 hours - #11 by Melissa

image
image1491×160 9.02 KB

Net Duration incl workhours, calculates the difference between the Min and Max datetime values and excludes non-workinghours, weekends and if provided holidays.

Net Duration excl non workdays, calculates the difference between the Min and Max datetime values and excludes weekends and if provided holidays.

Simple Duration, calculates the difference between the Min and Max datetime values.

.
Hope this is helpful.
eDNA - Identify data sets to keep (5).pbix (53.5 KB)

1 Like
49

thank you both so much for your help!! I got this sorted it seems for my actual data. Really appreciate your guidance and def thanks for your patience! i learned so much :slight_smile: Stay safe guys.

50

@Jetuke_ES,

That’s great to hear. I really enjoyed working with you and @Melissa on this one, and learned a lot myself. Good stuff! :+1:

  • Brian
1 Like