Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Sum column for dates within period

Hi, everybody!

There are two simple tables:

events:

d_user_iddatecount
12019-04-015
12022-05-0119
22020-01-0178
32020-05-0122
32018-05-0132
42021-02-0533
42018-05-0222
12020-01-0114

 

periods:

d_user_idstart_dateend_date
12020-04-012020-05-31
12019-03-012019-05-01
32018-01-012018-12-31
42021-02-012021-03-01
42021-03-012022-05-05
52020-09-252021-01-01
52020-08-012020-09-01
22018-01-012021-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.

Need Help
Tutorial Requests
  • daxer-almighty's avatar
    daxer-almighty
    5 years ago
    // 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 )
        )
    )
)

4 Replies

  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    5 years ago
    // 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's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks a lot!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        daxer-almighty
        Icon for Solution Sage rankSolution Sage
        5 years ago

        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)