topic Re: Events in progress with proportional count in DAX Commands and Tips

Events in progress with proportional count

jori — Thu, 16 Feb 2023 14:20:39 GMT

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

Re: Events in progress with proportional count

DataInsights — Mon, 20 Feb 2023 16:35:58 GMT

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

Re: Events in progress with proportional count

jori — Tue, 21 Feb 2023 08:39:01 GMT

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

Re: Events in progress with proportional count

jori — Tue, 21 Feb 2023 08:40:41 GMT

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

Re: Events in progress with proportional count

jori — Tue, 21 Feb 2023 08:41:25 GMT

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 🙂

Re: Events in progress with proportional count

jori — Tue, 21 Feb 2023 08:43:46 GMT

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

Re: Events in progress with proportional count

DataInsights — Tue, 21 Feb 2023 19:17:43 GMT

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 vResult

If 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.

Re: Events in progress with proportional count

jori — Wed, 22 Feb 2023 16:26:46 GMT

Hi DataInsights

this is not working either, the count is actually a bit higher than the regular one.

Sample:

https://www.dropbox.com/scl/fi/fh11e3xvt3h6fuo32ua38/sample.xlsx?dl=0&rlkey=x2vbeft2lvcn2v1d8n9rpryh0

There is also a link to a standard date table from the CreatedDate.

Thank you

Re: Events in progress with proportional count

DataInsights — Wed, 22 Feb 2023 21:08:05 GMT

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?

Re: Events in progress with proportional count

jori — Tue, 28 Feb 2023 09:01:32 GMT

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:

https://www.dropbox.com/scl/fi/byqrxy8ztyoeufudbb7xr/sample2.xlsx?dl=0&rlkey=o8gs9jgswfw09vly01ht7tfnt

I also uploaded a date table:

https://www.dropbox.com/scl/fi/y5zypach43qniybw74jsg/Date-Table.xlsx?dl=0&rlkey=7al46n0papcuf8r3fnqnmzxs6

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:

https://www.dropbox.com/scl/fi/q6s09uq8bdqb0dvn7x17l/sample2-expected-result.xlsx?dl=0&rlkey=gh5voibfgxukhkug6v141s2lt

I hope this helps clearing up the problem and thank you for your help 🙂

Re: Events in progress with proportional count

jori — Tue, 28 Feb 2023 09:07:03 GMT

Oh and regarding the duplicates: You can leave them in, since every OpportunityId should only be counted once this should not change the result.