Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Dates without the Weekends

Hello guys, I would like to calculate a date in Power Bi that does not include the weekend days. A quick example: I have today's date 06/17/21 and want to sum up 5 business days on the date. That...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Please check the below picture and the sample pbix file's link down below.

     

     

    delivery date measure =
    VAR dateranking =
    RANKX (
    FILTER ( ALL ( Dates ), Dates[Day of Week] <> 6 && Dates[Day of Week] <> 0 ),
    CALCULATE ( MAX ( Dates[Date] ) ),
    ,
    ASC
    )
    VAR selecteddays =
    SELECTEDVALUE ( 'Until Delivery'[Until Delivery] )
    RETURN
    MAXX (
    FILTER (
    ALL ( Dates ),
    RANKX (
    FILTER ( ALL ( Dates ), Dates[Day of Week] <> 6 && Dates[Day of Week] <> 0 ),
    CALCULATE ( MAX ( Dates[Date] ) ),
    ,
    ASC
    ) = dateranking + selecteddays
    ),
    Dates[Date]
    )

     

     

    https://www.dropbox.com/s/7bkng1984zav4qm/keluv2.pbix?dl=0 

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, Anonymous 

Please check the below picture and the sample pbix file's link down below.

 

 

delivery date measure =
VAR dateranking =
RANKX (
FILTER ( ALL ( Dates ), Dates[Day of Week] <> 6 && Dates[Day of Week] <> 0 ),
CALCULATE ( MAX ( Dates[Date] ) ),
,
ASC
)
VAR selecteddays =
SELECTEDVALUE ( 'Until Delivery'[Until Delivery] )
RETURN
MAXX (
FILTER (
ALL ( Dates ),
RANKX (
FILTER ( ALL ( Dates ), Dates[Day of Week] <> 6 && Dates[Day of Week] <> 0 ),
CALCULATE ( MAX ( Dates[Date] ) ),
,
ASC
) = dateranking + selecteddays
),
Dates[Date]
)

 

 

https://www.dropbox.com/s/7bkng1984zav4qm/keluv2.pbix?dl=0