Measure Problem in calculating a delay

[Anonymous]

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.

[v-jingzhang · Accepted solution]

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.