Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculate orders from next day, exclude weekends

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

Need Help
Show and Tell
Tips and Tricks

4 Replies

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

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        v-zhangti
        Icon for Community Support rankCommunity Support
        4 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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