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

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

amitchandak thank you for that formula. I am getting the error, "The start date in Calendar function can not be later than the end date."

I am working with some projects that do not have a "DATE OUT" entered yet. Could this be the cause of that problem? That is the main reason why I had the VAR DATEOUTBLANK in my formula before.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Matt_Mohawk , add an if on top of it and check if it works. or share small sample with expected output

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

      Below is an example of what I am needing. The areas marked in red show that right now it is counting as 2 days, this is actually supposed to be 1 day because we do not count the day that it comes into us. The green area is the area that I want the formula to ignore because if a project comes in and goes out on the same day, it needs to be counted as 1 day. Hope this makes sense.

       

      • 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