Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Query to exclude Saturdays and Sundays depending on condition in counting of ageing

I have this DAX query where in:
If 'WeekendBanking' column is blank/empty, exclude saturdays and sundays (based on networkdays function),
else exclude only Sunday.

 

 

NetworkDays =
VAR IsWeekendBanking = NOT(ISBLANK('Branch Daily Certification - Test'[WeekendBanking]))
VAR ToBeSubmittedDate = 'Branch Daily Certification - Test'[forDate]
VAR SubmittedDate = 'Branch Daily Certification - Test'[dateSubmitted2]

RETURN
IF(
    ISBLANK(ToBeSubmittedDate) || ISBLANK(SubmittedDate),
    BLANK(),
    IF(
        ISBLANK(IsWeekendBanking),
        NETWORKDAYS(ToBeSubmittedDate, SubmittedDate, 11),  -- Exclude only Sundays
        NETWORKDAYS(ToBeSubmittedDate, SubmittedDate, 1)  -- Exclude both Saturdays and Sundays
    )
)

 

 

 

 

This is the output:

 

 

As you can see, if the Branch has a weekend banking, the ageing is correct- it excludes only Sundays.
But if it doesn't, I'm not so sure. For example, for the Sen. Gil Puyat branch (the very last item), the forDate is 08/30 and dateSubmitted is 08/31. Shouldn't be the NetworkDays (Ageing) be 1? Or is it really 2 (correct)



Thanks for the help.

  • Hi, Anonymous 

     

    You can try the following methods.

    Column = IF([WeekendBanking]=BLANK(),NETWORKDAYS([forDate],[dateSubmitted2],1),NETWORKDAYS([forDate],[dateSubmitted2],11))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support

    Hi, Anonymous 

     

    You can try the following methods.

    Column = IF([WeekendBanking]=BLANK(),NETWORKDAYS([forDate],[dateSubmitted2],1),NETWORKDAYS([forDate],[dateSubmitted2],11))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.