Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure Problem in calculating a delay

Dear PowerBi community:

I would like to ask for your help in correcting a formula that does not work correctly.

What I would like to create is a measure that computes the a delay through a series of weeks.

I think it would be clearer if I show you an example:

 

I have two dates column (Planned and Actual) associeted to a document:

Because Date Actual is higher than Planned the document is in delay.

So, in my visual I should obtain for  January 2 (end of the first week) 6 days of delay

for 9 january 13 days of delay, and so on.

Then, I would like to repete this procedure for a wider list of document.

For instance, the 30th of January I will have the delay of Doc 3 and Doc 8 (12 days) + the delay of  Doc 7

This calculation should be done until the date of actual. Therefore, after February 24 I should not have any overdue document.

 

For this scope I created this measure:

With this calendar table:

and with these reletionships:

What I obtain is this:

The Doc Code 7 of 2rd of January is 6 Days late (which is correct).

However, when I look at the 9th of january the delay of this document is not recorded in the visual (there should be 13 days of delay as I said before). The next value which is shown in the visual refers to the others 2 documents planned for 24 of january (for which the actual date is later in February) and the delay is 12 days ( and therefore it still does't take into account the delay of doc Code 7 which was received the 9th of February).

Do you have any idea how I can correct my measure?

Sincerely,

Francesco.

  • Hi Anonymous

     

    Please try this measure if I understand it correctly. And keep two tables disconnected

    Delay = 
    VAR DelayPerWeek =
        SUMX (
            Sheet1,
            IF (
                MAX ( 'Calendar'[WeekEnding] ) > Sheet1[Planned]
                    && (
                        MAX ( 'Calendar'[WeekEnding] ) < Sheet1[Actual]
                            || ISBLANK ( Sheet1[Actual] )
                    ),
                DATEDIFF ( Sheet1[Planned], MAX ( 'Calendar'[WeekEnding] ), DAY ),
                0
            )
        )
    VAR Limit_Calendar =
        MAX ( 'Calendar'[Date] )
    RETURN
        IF ( TODAY () > Limit_Calendar, DelayPerWeek / 7, BLANK () )
    

     

    If you put the variable DelayPerWeek into a single measure, you will see its result which is correct. 

     

    I have attached my sample file for your reference. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support

    Hi Anonymous

     

    Please try this measure if I understand it correctly. And keep two tables disconnected

    Delay = 
    VAR DelayPerWeek =
        SUMX (
            Sheet1,
            IF (
                MAX ( 'Calendar'[WeekEnding] ) > Sheet1[Planned]
                    && (
                        MAX ( 'Calendar'[WeekEnding] ) < Sheet1[Actual]
                            || ISBLANK ( Sheet1[Actual] )
                    ),
                DATEDIFF ( Sheet1[Planned], MAX ( 'Calendar'[WeekEnding] ), DAY ),
                0
            )
        )
    VAR Limit_Calendar =
        MAX ( 'Calendar'[Date] )
    RETURN
        IF ( TODAY () > Limit_Calendar, DelayPerWeek / 7, BLANK () )
    

     

    If you put the variable DelayPerWeek into a single measure, you will see its result which is correct. 

     

    I have attached my sample file for your reference. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.