cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Reply
jori
Frequent Visitor

Events in progress with proportional count

‎02-16-2023 06:16 AM

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

Message 1 of 11
694 Views
0
10 REPLIES 10
jori
Frequent Visitor

‎02-28-2023 01:07 AM

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

Message 11 of 11
375 Views
0
jori
Frequent Visitor

‎02-22-2023 08:26 AM

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

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

 

Thank you

Message 8 of 11
489 Views
0
DataInsights
Super User
Super User
In response to jori

‎02-22-2023 01:08 PM

@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?

 

DataInsights_0-1677099790619.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 9 of 11
473 Views
0
jori
Frequent Visitor
In response to DataInsights

‎02-28-2023 01:01 AM

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

jori_0-1677573462169.png

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 🙂

Message 10 of 11
379 Views
0
jori
Frequent Visitor

‎02-21-2023 12:43 AM

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

Message 6 of 11
548 Views
0
DataInsights
Super User
Super User
In response to jori

‎02-21-2023 11:17 AM

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 7 of 11
525 Views
0
jori
Frequent Visitor

‎02-21-2023 12:41 AM

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 🙂

Message 5 of 11
553 Views
0
jori
Frequent Visitor

‎02-21-2023 12:40 AM

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

 

Message 4 of 11
556 Views
0
jori
Frequent Visitor

‎02-21-2023 12:39 AM

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.

 

OpportunityIdCreatedDateEndDate
0067U000005T4MFQA026.12.2022 21:4026.12.2022 21:40
0067U000005T4MFQA026.12.2022 21:4026.12.2022 21:40
0067U000005T4KiQAK01.01.2023 19:0801.01.2023 19:10
0067U000005T4KiQAK01.01.2023 19:1001.01.2023 19:10
0060900000P53jzAAB28.12.2022 07:4105.01.2023 12:28
0060900000P2bqEAAR19.12.2022 13:3608.04.2023 00:00
0060900000P4R4VAAV14.12.2022 13:2119.01.2023 14:49
0060900000P4DZeAAN16.12.2022 13:1004.01.2023 07:39
0060900000P3T0PAAV21.12.2022 12:2611.01.2023 10:31
0060900000P4WZHAA321.12.2022 10:2005.01.2023 09:11
0060900000P4TNFAA323.12.2022 10:1502.01.2023 17:48
0060900000P41b4AAB06.12.2022 18:0502.01.2023 17:29
0067U000004gnMVQAY13.12.2022 11:3812.01.2023 15:42
0060900000P54QQAAZ28.12.2022 16:3529.12.2022 08:28
0060900000P54QQAAZ29.12.2022 08:2813.01.2023 14:22
0060900000P4IPKAA321.12.2022 10:3620.01.2023 10:14
0060900000P4FvgAAF08.12.2022 17:5510.01.2023 09:53
0060900000P3mzkAAB27.12.2022 16:0403.01.2023 16:50
0060900000P3mzkAAB22.12.2022 11:2227.12.2022 16:04
0060900000P4NsOAAV12.12.2022 17:1704.01.2023 10:15
0060900000P56lNAAR29.12.2022 12:2204.01.2023 09:13
0060900000P4DoxAAF08.12.2022 08:4202.01.2023 15:47
0060900000P41IWAAZ08.12.2022 10:5401.02.2023 12:07
0060900000P3CmsAAF20.12.2022 11:0231.01.2023 13:13
0060900000P2tDXAAZ01.12.2022 16:3802.01.2023 16:09
0060900000P53mZAAR28.12.2022 08:2630.12.2022 09:32
0060900000P53mZAAR30.12.2022 09:3216.01.2023 10:34
0060900000P2i90AAB15.11.2022 17:2416.01.2023 15:33
0060900000P4DhvAAF16.12.2022 15:3204.01.2023 10:11
0060900000P58weAAB30.12.2022 08:5203.01.2023 12:02
0060900000P478fAAB07.12.2022 10:2406.01.2023 07:30
0060900000P56YYAAZ29.12.2022 09:1502.01.2023 08:51
0060900000P3njcAAB19.12.2022 09:5404.01.2023 11:44
0060900000P4OqmAAF12.12.2022 17:0602.01.2023 11:04
0067U000005WQKyQAO02.12.2022 10:3211.01.2023 08:44
0060900000P3mkkAAB29.11.2022 09:0011.01.2023 16:35
0067U000005W29dQAC25.11.2022 13:2002.01.2023 15:40
0067U000005Ux2hQAC23.12.2022 14:3902.01.2023 14:16
0067U000004i1VaQAI08.12.2022 11:4802.01.2023 11:38
0060900000P4Aq0AAF27.12.2022 15:1304.01.2023 09:29
0060900000P4Aq0AAF21.12.2022 09:4527.12.2022 15:13
0067U000005x6jNQAQ28.12.2022 16:0228.12.2022 16:02
0067U000005x6jNQAQ23.12.2022 09:2428.12.2022 16:02
0067U000005WSmIQAW15.12.2022 16:3510.01.2023 19:11
0060900000P4B98AAF19.12.2022 14:1002.01.2023 17:24
0060900000P4WrNAAV16.12.2022 10:2210.01.2023 10:39

 

 

Message 3 of 11
559 Views
0
DataInsights
Super User
Super User

‎02-20-2023 08:35 AM

@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

 

DataInsights_0-1676910793441.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 2 of 11
575 Views
0

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All