Calculate orders from next day, exclude weekends

[Anonymous]

Hi,

I am trying to count the amount of shipments I have for the next day. Formula I used did work, but when it is Friday it calculates orders for saturday instead of Monday which is obvious as I added a TODAY()+1 in my measure. When Friday it should be +3 and when it is Saturday it should be +2.

My Date table:

My Measure:

_Workload Today =

CALCULATE(

Distinctcount(G002_KDE_BI_Report[Shipment No.]),

'G002_KDE_BI_Report'[Pick up date/time] = TODAY(),

'G002_KDE_BI_Report'[_Bin] IN {"Consolidation" , "Consolidated" , "Slot booking" , "Manifest"})

For workload next day as said it must calculate 3 days if today is Friday, 2 days if today is Saturday. @amitchandak maybe? I used your date table from Week Not so Weak Blog.

Kind Regards,

Sofien

[Anonymous]

Hi @v-zhangti, 

Did create the file with sensitive data for this visual and measure deleted. Need to look for a solution though as my company blocks filehosting websites and I am not able to upload a PBIX file here apparently as an attachment to my reply. If not able to upload PBIX file here, I will do it later today from my private laptop.

Kind Regards,

Sofiën

[v-zhangti]

Hi, @Anonymous 

You can try the following methods.

Measure:

Workload next day = 
IF (
    WEEKDAY ( TODAY (), 2 ) = 5,
    CALCULATE (
        DISTINCTCOUNT ( G002_KDE_BI_Report[Shipment No] ),
        FILTER (
            ALL ( 'G002_KDE_BI_Report'[Pick up date] ),
            [Pick up date] >= TODAY ()
                && [Pick up date]
                    <= TODAY () + 3
        )
    ),
    IF (
        WEEKDAY ( TODAY (), 2 ) = 6,
        CALCULATE (
            DISTINCTCOUNT ( G002_KDE_BI_Report[Shipment No] ),
            FILTER (
                ALL ( 'G002_KDE_BI_Report'[Pick up date] ),
                [Pick up date] >= TODAY ()
                    && [Pick up date]
                        <= TODAY () + 2
            )
        ),
        IF (
            WEEKDAY ( TODAY (), 2 ) = 7,
            CALCULATE (
                DISTINCTCOUNT ( G002_KDE_BI_Report[Shipment No] ),
                FILTER (
                    ALL ( 'G002_KDE_BI_Report'[Pick up date] ),
                    [Pick up date] >= TODAY ()
                        && [Pick up date]
                            <= TODAY () + 1
                )
            ),
            CALCULATE (
                DISTINCTCOUNT ( G002_KDE_BI_Report[Shipment No] ),
                FILTER ( ALL ( 'G002_KDE_BI_Report'[Pick up date] ), [Pick up date] = TODAY () )
            )
        )
    )
)

As an example, I hope you can understand this formula better. Suppose today is Friday, May 20.

Date = CALENDAR(MIN(G002_KDE_BI_Report[Pick up date]),DATE(2022,5,20))

Measure Test = 
IF (
    WEEKDAY ( DATE ( 2022, 5, 20 ), 2 ) = 5,
    CALCULATE (
        DISTINCTCOUNT ( G002_KDE_BI_Report[Shipment No] ),
        FILTER (
            ALL ( 'G002_KDE_BI_Report'[Pick up date] ),
            [Pick up date] >= DATE ( 2022, 5, 20 )
                && [Pick up date]
                    <= DATE ( 2022, 5, 20 ) + 3
        )
    )
)

   

Please check if this is 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.

[Anonymous]

Hi @v-zhangti,

Thanks for helping out, measure works as well, but it was not exactly what I was looking for. I added the pick up date and time column to the matrix table. And on the right filter pane, I want to show only values from today + the next day, this works fine until it's Friday. Then it should take Monday into account as next day instead of Saturday.

This is the current measure I have : 

_Workload = CALCULATE(Distinctcount(G002_KDE_BI_Report[Shipment No.]))

I added the PickUp_date and PickUp_Time column to the rows from the Matrix table. Currently it is displayed as:

Output is fine because today is Monday and it shows data from today + Tuesday. Filter I added on this visual = 

This filter must calculate next 2 days include today and not take weekends into account.

Kind Regards,

Sofiën

[v-zhangti]

Hi, @Anonymous 

Is it possible to share your PBIX files? Please remove sensitive information in advance. What kind of results do you expect after applying the filter?

Best Regards