Forum Discussion

Matt_Mohawk's avatar
Matt_Mohawk
Icon for Helper II rankHelper II
5 years ago
Solved

Number of Days Calculation

I am trying to figure out an issue with a formula that I have created. I am needing to modify this formula to add an extra day to the "DATE IN" field but ignore dates that have the same "DATE IN" and...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Matt_Mohawk ,

    Please try to update the formula of your calculated column [# of days] as below:

    # of Days = 
VAR _days =
    CALCULATE (
        SUM ( 'Date'[Workday Not Holiday] ),
        DATESBETWEEN ( 'Date'[Date], MJS_PRICE_PW[DATE IN], MJS_PRICE_PW[DATE OUT] )
    )
RETURN
    IF (
        ISBLANK ( MJS_PRICE_PW[DATE OUT] ),
        BLANK (),
        IF (
            MJS_PRICE_PW[DATE IN] = MJS_PRICE_PW[DATE OUT],
            1,
            _days
                - IF ( NOT ( WEEKDAY ( MJS_PRICE_PW[DATE IN], 1 ) IN { 1, 7 } ), 1, 0 )
        )
    )

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Matt_Mohawk ,

Please try to update the formula of your calculated column [# of days] as below:

# of Days = 
VAR _days =
    CALCULATE (
        SUM ( 'Date'[Workday Not Holiday] ),
        DATESBETWEEN ( 'Date'[Date], MJS_PRICE_PW[DATE IN], MJS_PRICE_PW[DATE OUT] )
    )
RETURN
    IF (
        ISBLANK ( MJS_PRICE_PW[DATE OUT] ),
        BLANK (),
        IF (
            MJS_PRICE_PW[DATE IN] = MJS_PRICE_PW[DATE OUT],
            1,
            _days
                - IF ( NOT ( WEEKDAY ( MJS_PRICE_PW[DATE IN], 1 ) IN { 1, 7 } ), 1, 0 )
        )
    )

Best Regards

Matt_Mohawk's avatar
Matt_Mohawk
Icon for Helper II rankHelper II
5 years ago

Anonymous That did it! You are a lifesaver! Now I only have to figure out how you got it to work so I can learn to do this for later jobs.