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_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-0...
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 )
        )
    )
)
daxer-almighty's avatar
daxer-almighty
Solution 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
      Solution 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)