Forum Discussion
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-jingzhangCommunity 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.