cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## 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 10
Frequent Visitor

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

Frequent Visitor

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=x2vbeft2lvcn2v1d8n9rpryh...

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

Thank you

Super User

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?

Proud to be a Super User!

Frequent Visitor

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=o8gs9jgswfw09vly01ht7tf...

I also uploaded a date table:

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

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=gh5voib...

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

Frequent Visitor

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

Super User

Try this:

``````_CountNew =
VAR MinDate =
MIN ( Datumstabelle[FullDateAlternateKey] )
VAR MaxDate =
MAX ( Datumstabelle[FullDateAlternateKey] ) + TIME ( 23, 59, 59 )
VAR vTable =
CALCULATETABLE (
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.

Proud to be a Super User!

Frequent Visitor

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 🙂

Frequent Visitor

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

Frequent Visitor

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 =
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

Super User

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 =
SUMMARIZE ( Table1, Table1[Id], Table1[Week] ),
"@MinFactor", CALCULATE ( MAX ( Table1[Min Factor] ) )
)
VAR vResult =
SUMX ( vTable, [@MinFactor] )
RETURN
vResult``````

Proud to be a Super User!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.