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