Forum Discussion
Events in progress with proportional count
I'm trying to count the events in progress as described here: https://www.daxpatterns.com/events-in-progress/
But instead of having each event count distinctly, I would like them to be counted proportionally, depending on which day of the week they were finished:
- Monday: 0.2
- Tuesday: 0.4
- Wednesday: 0.6
- Thursday: 0.8
- Friday/Saturday/Sunday: 1
So, for example, if there's an event that goes from week 1 and ends in week 3 on a Tuesday, the event should be counted as 1 in week 1, as 1 in week 2 and as 0.4 in week 3.
The code for the distinct count is as follows:
_Count =
VAR MinDate =
MIN ( Datumstabelle[FullDateAlternateKey] )
VAR MaxDate =
MAX ( Datumstabelle[FullDateAlternateKey] ) + TIME ( 23, 59, 59 )
RETURN
CALCULATE (
DISTINCTCOUNT ( SF_Opportunityfeldverlauf[OpportunityId] ),
SF_Opportunityfeldverlauf[CreatedDate] <= MaxDate,
SF_Opportunityfeldverlauf[EndDate] > MinDate,
REMOVEFILTERS ( Datumstabelle )
)
Also the SF_Opportunityfeldverlauf table is linked to a date table (Datumstabelle) n:1 via the CreatedDate field.
I started by adding a column from the date table to have the calendar week in SF_Opportunityfeldverlauf and then calculating the factor for each Id and week depending on the day of the week of CreatedDate. Then in another column I put the minium factor for each Id and week.
Simplified example:
Id | Week | Factor | Min Factor |
A | 1 | 1 | 1 |
A | 2 | 1 | 0.4 |
A | 2 | 0.4 | 0.4 |
B | 1 | 1 | 1 |
B | 2 | 0.2 | 0.2 |
Expected result:
Week 1 | 2 |
Week 2 | 0.6 |
In the formula above, I changed the DISTINCTCOUNT to a SUMX over the OpportunityId and the factor like this:
_CountProp =
VAR MinDate =
MIN ( Datumstabelle[FullDateAlternateKey] )
VAR MaxDate =
MAX ( Datumstabelle[FullDateAlternateKey] ) + TIME ( 23, 59, 59 )
RETURN
CALCULATE (
SUMX (
SUMMARIZE (
SF_Opportunityfeldverlauf,
SF_Opportunityfeldverlauf[OpportunityId],
SF_Opportunityfeldverlauf[Min Factor]
),
SF_Opportunityfeldverlauf[Min Factor]
),
SF_Opportunityfeldverlauf[CreatedDate] <= MaxDate,
SF_Opportunityfeldverlauf[EndDate] > MinDate,
REMOVEFILTERS ( Datumstabelle )
)
But the result is not correct because it counts the opportunities more than once per week. I somehow need to tell Power BI to only count each opportunity once per calendar week multiplied by the Min Factor for that opportunity of that calendar week.
Does anyone know how to do this?
Sample Data:
OpportunityId | CreatedDate | EndDate | OldValue | NewValue | Factor | ISO KW Erstelldatum | Min Factor |
0067U000002cLQVQA2 | 01.08.2022 13:23 | 02.08.2022 14:46 | Needs Analysis | Proposal | 1 | 2022 KW 31 | 1 |
0067U000002b4PAQAY | 29.07.2022 13:22 | 29.07.2022 13:22 | Negotiation | On Hold | 1 | 2022 KW 30 | 1 |
0067U000002bj38QAA | 29.07.2022 11:42 | 02.08.2022 08:06 | Proposal | Negotiation | 1 | 2022 KW 30 | 1 |
0067U000002c5N4QAI | 29.07.2022 08:37 | 29.07.2022 08:37 | Proposal | Closed Lost | 1 | 2022 KW 30 | 1 |
0067U000002c5N4QAI | 29.07.2022 08:26 | 29.07.2022 08:37 | Needs Analysis | Proposal | 1 | 2022 KW 30 | 1 |
0067U000002c4rrQAA | 28.07.2022 14:14 | 28.07.2022 14:14 | Needs Analysis | On Hold | 0,8 | 2022 KW 30 | 0,8 |
0067U000002b5ZfQAI | 27.07.2022 12:57 | 08.08.2022 16:10 | Proposal | Negotiation | 1 | 2022 KW 30 | 1 |
0067U000002bj38QAA | 25.07.2022 08:31 | 29.07.2022 11:42 | Needs Analysis | Proposal | 1 | 2022 KW 30 | 1 |
0067U000002bP2KQAU | 20.07.2022 13:58 | 20.07.2022 13:58 | Needs Analysis | On Hold | 0,6 | 2022 KW 29 | 0,6 |
0067U000002b5ZfQAI | 14.07.2022 13:37 | 27.07.2022 12:57 | Needs Analysis | Proposal | 1 | 2022 KW 28 | 1 |
0067U000002b4PAQAY | 14.07.2022 13:32 | 29.07.2022 13:22 | Proposal | Negotiation | 1 | 2022 KW 28 | 1 |
0067U000002b4PAQAY | 13.07.2022 16:02 | 14.07.2022 13:32 | Needs Analysis | Proposal | 1 | 2022 KW 28 | 1 |
0067U000005G7CEQA0 | 06.01.2023 08:06 | 06.01.2023 08:06 | Proposal | Closed Lost | 1 | 2023 KW 01 | 1 |
0067U000005G7CEQA0 | 06.01.2023 08:05 | 06.01.2023 08:06 | On Hold | Proposal | 1 | 2023 KW 01 | 1 |
0067U000005G7CEQA0 | 12.12.2022 15:33 | 12.12.2022 15:33 | Proposal | On Hold | 0,2 | 2022 KW 50 | 0,2 |
0067U000005G7CEQA0 | 12.12.2022 15:26 | 12.12.2022 15:33 | On Hold | Proposal | 1 | 2022 KW 50 | 0,2 |
0060900000P3JAzAAN | 05.12.2022 07:52 | 05.12.2022 07:52 | Proposal | Closed Lost | 0,2 | 2022 KW 49 | 0,2 |
0060900000P3JAzAAN | 24.11.2022 11:15 | 05.12.2022 07:52 | Needs Analysis | Proposal | 1 | 2022 KW 47 | 1 |
0060900000PQif2AAD | 18.01.2023 13:38 | 18.01.2023 13:38 | Proposal | Closed Lost | 0,6 | 2023 KW 03 | 0,6 |
0060900000PQif2AAD | 13.01.2023 13:27 | 18.01.2023 13:38 | Needs Analysis | Proposal | 1 | 2023 KW 02 | 1 |
0067U000005x7eLQAQ | 16.11.2022 12:24 | 16.11.2022 12:24 | Proposal | Closed Lost | 0,6 | 2022 KW 46 | 0,6 |
0067U000005x7eLQAQ | 07.11.2022 09:28 | 16.11.2022 12:24 | Needs Analysis | Proposal | 1 | 2022 KW 45 | 1 |
0060900000P4W3yAAF | 21.12.2022 15:49 | 21.12.2022 15:49 | Proposal | Closed Lost | 0,6 | 2022 KW 51 | 0,6 |
0060900000P4W3yAAF | 21.12.2022 15:48 | 21.12.2022 15:49 | Negotiation | Proposal | 1 | 2022 KW 51 | 0,6 |
0060900000P4W3yAAF | 19.12.2022 09:53 | 21.12.2022 15:48 | Proposal | Negotiation | 1 | 2022 KW 51 | 0,6 |
0060900000P4W3yAAF | 15.12.2022 16:00 | 19.12.2022 09:53 | Needs Analysis | Proposal | 1 | 2022 KW 50 | 1 |
10 Replies
- DataInsights
Super User
jori,
Here's a measure for the simplified example. I used MAX to get the Min Factor, but you could use MIN or VALUES.
_SumMinFactor = VAR vTable = ADDCOLUMNS ( SUMMARIZE ( Table1, Table1[Id], Table1[Week] ), "@MinFactor", CALCULATE ( MAX ( Table1[Min Factor] ) ) ) VAR vResult = SUMX ( vTable, [@MinFactor] ) RETURN vResult Hi DataInsights
thank you for your answer.
While it does count the factor correctly, this measure doesn't work in combination with the events in progress.
I will try to explain:
This is the measure I used:
_CountNew = VAR MinDate = MIN(Datumstabelle[FullDateAlternateKey]) VAR MaxDate = MAX(Datumstabelle[FullDateAlternateKey]) + TIME(23, 59, 59) VAR vTable = ADDCOLUMNS ( SUMMARIZE ( SF_Opportunityfeldverlauf, SF_Opportunityfeldverlauf[OpportunityId], SF_Opportunityfeldverlauf[ISO KW Erstelldatum] ), "@MinFactor", CALCULATE ( MAX ( SF_Opportunityfeldverlauf[Min Faktor KW] ) ) ) VAR vResult = SUMX ( vTable, [@MinFactor] ) RETURN CALCULATE( vResult, SF_Opportunityfeldverlauf[CreatedDate] <= MaxDate, SF_Opportunityfeldverlauf[EndDate] > MinDate, REMOVEFILTERS(Datumstabelle) )I use the CreatedDate and EndDate to calculcate the events in progress. Using the following data as an example for just one week (2022 KW 52) and the regular DISTINTCOUNT the result is 39 (each Id in the table counted once yields exactly that result). This is using the regular events in progress.
OpportunityId CreatedDate EndDate 0067U000005T4MFQA0 26.12.2022 21:40 26.12.2022 21:40 0067U000005T4MFQA0 26.12.2022 21:40 26.12.2022 21:40 0067U000005T4KiQAK 01.01.2023 19:08 01.01.2023 19:10 0067U000005T4KiQAK 01.01.2023 19:10 01.01.2023 19:10 0060900000P53jzAAB 28.12.2022 07:41 05.01.2023 12:28 0060900000P2bqEAAR 19.12.2022 13:36 08.04.2023 00:00 0060900000P4R4VAAV 14.12.2022 13:21 19.01.2023 14:49 0060900000P4DZeAAN 16.12.2022 13:10 04.01.2023 07:39 0060900000P3T0PAAV 21.12.2022 12:26 11.01.2023 10:31 0060900000P4WZHAA3 21.12.2022 10:20 05.01.2023 09:11 0060900000P4TNFAA3 23.12.2022 10:15 02.01.2023 17:48 0060900000P41b4AAB 06.12.2022 18:05 02.01.2023 17:29 0067U000004gnMVQAY 13.12.2022 11:38 12.01.2023 15:42 0060900000P54QQAAZ 28.12.2022 16:35 29.12.2022 08:28 0060900000P54QQAAZ 29.12.2022 08:28 13.01.2023 14:22 0060900000P4IPKAA3 21.12.2022 10:36 20.01.2023 10:14 0060900000P4FvgAAF 08.12.2022 17:55 10.01.2023 09:53 0060900000P3mzkAAB 27.12.2022 16:04 03.01.2023 16:50 0060900000P3mzkAAB 22.12.2022 11:22 27.12.2022 16:04 0060900000P4NsOAAV 12.12.2022 17:17 04.01.2023 10:15 0060900000P56lNAAR 29.12.2022 12:22 04.01.2023 09:13 0060900000P4DoxAAF 08.12.2022 08:42 02.01.2023 15:47 0060900000P41IWAAZ 08.12.2022 10:54 01.02.2023 12:07 0060900000P3CmsAAF 20.12.2022 11:02 31.01.2023 13:13 0060900000P2tDXAAZ 01.12.2022 16:38 02.01.2023 16:09 0060900000P53mZAAR 28.12.2022 08:26 30.12.2022 09:32 0060900000P53mZAAR 30.12.2022 09:32 16.01.2023 10:34 0060900000P2i90AAB 15.11.2022 17:24 16.01.2023 15:33 0060900000P4DhvAAF 16.12.2022 15:32 04.01.2023 10:11 0060900000P58weAAB 30.12.2022 08:52 03.01.2023 12:02 0060900000P478fAAB 07.12.2022 10:24 06.01.2023 07:30 0060900000P56YYAAZ 29.12.2022 09:15 02.01.2023 08:51 0060900000P3njcAAB 19.12.2022 09:54 04.01.2023 11:44 0060900000P4OqmAAF 12.12.2022 17:06 02.01.2023 11:04 0067U000005WQKyQAO 02.12.2022 10:32 11.01.2023 08:44 0060900000P3mkkAAB 29.11.2022 09:00 11.01.2023 16:35 0067U000005W29dQAC 25.11.2022 13:20 02.01.2023 15:40 0067U000005Ux2hQAC 23.12.2022 14:39 02.01.2023 14:16 0067U000004i1VaQAI 08.12.2022 11:48 02.01.2023 11:38 0060900000P4Aq0AAF 27.12.2022 15:13 04.01.2023 09:29 0060900000P4Aq0AAF 21.12.2022 09:45 27.12.2022 15:13 0067U000005x6jNQAQ 28.12.2022 16:02 28.12.2022 16:02 0067U000005x6jNQAQ 23.12.2022 09:24 28.12.2022 16:02 0067U000005WSmIQAW 15.12.2022 16:35 10.01.2023 19:11 0060900000P4B98AAF 19.12.2022 14:10 02.01.2023 17:24 0060900000P4WrNAAV 16.12.2022 10:22 10.01.2023 10:39 Sorry I have to split up the answer because of the character limit:
But instead of counting each Id once with factor 1, I would like it to be counted with the respective min factor of that week. So it should count only the following Ids distinctly resulting in a total of 36.8.
OpportunityId
CreatedDate
EndDate
ISO KW Erstelldatum
Min Faktor KW
0067U000005T4MFQA0
26.12.2022 21:40
26.12.2022 21:40
2022 KW 52
0,2
0067U000005T4KiQAK
01.01.2023 19:10
01.01.2023 19:10
2022 KW 52
1
0060900000P53jzAAB
28.12.2022 07:41
05.01.2023 12:28
2022 KW 52
1
0060900000P2bqEAAR
19.12.2022 13:36
08.04.2023 00:00
2022 KW 51
1
0060900000P4R4VAAV
14.12.2022 13:21
19.01.2023 14:49
2022 KW 50
1
0060900000P4DZeAAN
16.12.2022 13:10
04.01.2023 07:39
2022 KW 50
1
0060900000P3T0PAAV
21.12.2022 12:26
11.01.2023 10:31
2022 KW 51
1
0060900000P4WZHAA3
21.12.2022 10:20
05.01.2023 09:11
2022 KW 51
1
0060900000P4TNFAA3
23.12.2022 10:15
02.01.2023 17:48
2022 KW 51
1
0060900000P41b4AAB
06.12.2022 18:05
02.01.2023 17:29
2022 KW 49
1
0067U000004gnMVQAY
13.12.2022 11:38
12.01.2023 15:42
2022 KW 50
1
0060900000P54QQAAZ
28.12.2022 16:35
29.12.2022 08:28
2022 KW 52
1
0060900000P4IPKAA3
21.12.2022 10:36
20.01.2023 10:14
2022 KW 51
0,2
0060900000P4FvgAAF
08.12.2022 17:55
10.01.2023 09:53
2022 KW 49
1
0060900000P3mzkAAB
27.12.2022 16:04
03.01.2023 16:50
2022 KW 52
1
0060900000P4NsOAAV
12.12.2022 17:17
04.01.2023 10:15
2022 KW 50
1
0060900000P56lNAAR
29.12.2022 12:22
04.01.2023 09:13
2022 KW 52
1
0060900000P4DoxAAF
08.12.2022 08:42
02.01.2023 15:47
2022 KW 49
1
0060900000P41IWAAZ
08.12.2022 10:54
01.02.2023 12:07
2022 KW 49
0,8
0060900000P3CmsAAF
20.12.2022 11:02
31.01.2023 13:13
2022 KW 51
1
0060900000P2tDXAAZ
01.12.2022 16:38
02.01.2023 16:09
2022 KW 48
1
0060900000P53mZAAR
30.12.2022 09:32
16.01.2023 10:34
2022 KW 52
1
0060900000P2i90AAB
15.11.2022 17:24
16.01.2023 15:33
2022 KW 46
1
0060900000P4DhvAAF
16.12.2022 15:32
04.01.2023 10:11
2022 KW 50
1
0060900000P58weAAB
30.12.2022 08:52
03.01.2023 12:02
2022 KW 52
1
0060900000P478fAAB
07.12.2022 10:24
06.01.2023 07:30
2022 KW 49
1
0060900000P56YYAAZ
29.12.2022 09:15
02.01.2023 08:51
2022 KW 52
1
0060900000P3njcAAB
19.12.2022 09:54
04.01.2023 11:44
2022 KW 51
1
0060900000P4OqmAAF
12.12.2022 17:06
02.01.2023 11:04
2022 KW 50
1
0067U000005WQKyQAO
02.12.2022 10:32
11.01.2023 08:44
2022 KW 48
1
0060900000P3mkkAAB
29.11.2022 09:00
11.01.2023 16:35
2022 KW 48
1
0067U000005W29dQAC
25.11.2022 13:20
02.01.2023 15:40
2022 KW 47
1
0067U000005Ux2hQAC
23.12.2022 14:39
02.01.2023 14:16
2022 KW 51
1
0067U000004i1VaQAI
08.12.2022 11:48
02.01.2023 11:38
2022 KW 49
1
0060900000P4Aq0AAF
21.12.2022 09:45
27.12.2022 15:13
2022 KW 51
1
0067U000005x6jNQAQ
28.12.2022 16:02
28.12.2022 16:02
2022 KW 52
0,6
0067U000005WSmIQAW
15.12.2022 16:35
10.01.2023 19:11
2022 KW 50
1
0060900000P4B98AAF
19.12.2022 14:10
02.01.2023 17:24
2022 KW 51
1
0060900000P4WrNAAV
16.12.2022 10:22
10.01.2023 10:39
2022 KW 50
1
The measure above basically filters by 2022 KW 52 and then counts the Ids once with the respective factors resulting in a total of 9.8:
OpportunityId
ISO KW Erstelldatum
Min Faktor KW
0067U000005T4MFQA0
2022 KW 52
0,2
0067U000005T4KiQAK
2022 KW 52
1
0060900000P53jzAAB
2022 KW 52
1
0060900000P54QQAAZ
2022 KW 52
1
0060900000P3mzkAAB
2022 KW 52
1
0060900000P56lNAAR
2022 KW 52
1
0060900000P53mZAAR
2022 KW 52
1
0060900000P58weAAB
2022 KW 52
1
0060900000P56YYAAZ
2022 KW 52
1
0060900000P4Aq0AAF
2022 KW 52
1
0067U000005x6jNQAQ
2022 KW 52
0,6
I hope this helps clearing up the problem 🙂
So to summarize:
The regular formula results in 39 events in progress.
I would like it to be 36.8.
And the formula right now yields 9.8
- DataInsights
jori,
Try this:
_CountNew = VAR MinDate = MIN ( Datumstabelle[FullDateAlternateKey] ) VAR MaxDate = MAX ( Datumstabelle[FullDateAlternateKey] ) + TIME ( 23, 59, 59 ) VAR vTable = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( SF_Opportunityfeldverlauf, SF_Opportunityfeldverlauf[OpportunityId], SF_Opportunityfeldverlauf[ISO KW Erstelldatum] ), "@MinFactor", CALCULATE ( MAX ( SF_Opportunityfeldverlauf[Min Faktor KW] ) ) ), SF_Opportunityfeldverlauf[CreatedDate] <= MaxDate, SF_Opportunityfeldverlauf[EndDate] > MinDate, REMOVEFILTERS ( Datumstabelle ) ) VAR vResult = SUMX ( vTable, [@MinFactor] ) RETURN vResultIf this doesn't work, would you be able to provide the sample data in a table format (or a sample pbix via OneDrive, etc.)? The provided data in message 3 doesn't have the ISO KW Erstelldatum or Min Faktor KW columns.
Hi DataInsights
this is not working either, the count is actually a bit higher than the regular one.
Sample:
There is also a link to a standard date table from the CreatedDate.
Thank you
- DataInsights
jori,
Thanks for the sample data. For 2022 KW 52, what is the expected result, and what min and max dates should be used in the date slicer? I noticed that rows 2 and 3 below are duplicates. Is this correct or should duplicates be removed?
Hi DataInsights
sorry for the late response, I had a couple of days off.
I uploaded new sample data because it's too difficult to calulcate the expected result otherwise:
I also uploaded a date table:
You need to link the CreatedDate of the sample to the FullDateAlternateKey of the date table.
Then you can create a plot over time using the following measure:
_Count = VAR MinDate = MIN(Datumstabelle[FullDateAlternateKey]) VAR MaxDate = MAX(Datumstabelle[FullDateAlternateKey]) + TIME(23, 59, 59) RETURN CALCULATE( DISTINCTCOUNT(SF_Opportunityfeldverlauf[OpportunityId]), SF_Opportunityfeldverlauf[CreatedDate] <= MaxDate, SF_Opportunityfeldverlauf[EndDate] > MinDate, REMOVEFILTERS(Datumstabelle) )It is important to use the field ISOKalenderWocheJahr from the Date Table as x-axis, not from the sample data (that field was just used to calculate the factor).
The result should look like this:
This is the regular events in progress calculation that's correct and results in 39 events in 2022 KW 52.
Now what I would like is a measure to count the events in progress but instead of counting each event as 1 to use the respective factor for each week.
I uploaded another file with the expected result of 37.2 in 2022 KW 52:
I hope this helps clearing up the problem and thank you for your help 🙂
Oh and regarding the duplicates: You can leave them in, since every OpportunityId should only be counted once this should not change the result.
