Sum column for dates within period

[Anonymous]

Hi, everybody!

There are two simple tables:

events:

d_user_id	date	count
1	2019-04-01	5
1	2022-05-01	19
2	2020-01-01	78
3	2020-05-01	22
3	2018-05-01	32
4	2021-02-05	33
4	2018-05-02	22
1	2020-01-01	14

periods:

d_user_id	start_date	end_date
1	2020-04-01	2020-05-31
1	2019-03-01	2019-05-01
3	2018-01-01	2018-12-31
4	2021-02-01	2021-03-01
4	2021-03-01	2022-05-05
5	2020-09-25	2021-01-01
5	2020-08-01	2020-09-01
2	2018-01-01	2021-01-01

And there is the following task: create measure that would calculate SUM for column count of events for those rows that are in relevant period in table periods.

I've googled about it and found solution that recommends just to take MIN and MAX values of start and end period dates, but it's not suitable, because there are events that are not within any period, but if we calculate MIN and MAX such events will be summarized as well, but it's not correct.

Could you please help with this.

Thanks in advance.

Regards,

Daniil.

[daxer-almighty · Accepted solution]

// There has to be a dimension
// that holds all the users.
// It'll be connected to both
// tables, Events and Periods,
// on UserID with one-way filtering.
// There must not be any direct relationship
// between Events and Periods. They can
// only be linked via dimensions.

[Count for Events] =
SUMX(
    DISTINCT( Users[UserId] ),
    CALCULATE(
        SUMX(
            Events,
            var EventDate = Events[Date]
            VAR EventCount = Events[Count]
            var PeriodExists =
                NOT ISEMPTY(
                    FILTER(
                        Periods,
                        Periods[start_date] <= EventDate
                        &&
                        EventDate <= Periods[end_date]
                    )
                )
            return
                IF( PeriodExists, EventCount )
        )
    )
)

[Anonymous]

Hi, @daxer-almighty 
Looks like it can be simlplified a little.

Count for Events2 =

CALCULATE(

SUMX(

Events,

VAR PeriodExists =

NOT ISEMPTY(

FILTER(

Periods,

Events[Date] >= Periods[start_date]

&& Events[Date] <= Periods[end_date]

&& events[d_user_id] = periods[d_user_id]

)

)

RETURN

IF( PeriodExists, events[count] )

)

)

I've tested it on sample, seems to be working. What do you think?

Btw. How have you added code to your post? 🙂

[daxer-almighty]

Yeah... I think it'll work. To know which version will be faster requires testing, of course. Here's my version without the double summation:

[Count for Events] =
// Assuming that dimension Users filters
// Events and Periods on UserID...
VAR RawSum =
    SUMX(
        Events,
        VAR CurrentEventDate = Events[Date]
        VAR CurrentEventUser = Events[UserID]
        VAR CurrentEventCount = Events[Count]
        VAR EventExistsInPeriods =
            NOT ISEMPTY(
                FILTER(
                    Periods,
                    Periods[start_date] <= CurrentEventDate
                    && 
                    CurrentEventDate <= Periods[end_date]
                    && 
                    Periods[d_user_id] = CurrentEventUser
                )
            )
        RETURN
            EventExistsInPeriods * CurrentEventCount
    )
RETURN
    // This forces 0's to be returned
    // as BLANKs. Any number that's not
    // 0 is treated as TRUE.
    IF( RawSum, RawSum )

To format code you have to click

and then select C# (I've found this to be working best)

[Anonymous]

Thanks a lot!