Forum Discussion

flaviorangel's avatar
flaviorangel
New Member
3 years ago
Solved

DAX Cumulative sum in date interval

I have a table that registers log access (userIds and dates). I'm counting the number of users in a period of time by doing:

 

users_distinctcount = DISTINCTCOUNT(userLogTable[userId])
 
The entire page is filtered by a week range, so the above function returns the number of users in that period.
 
I would like to see the cumulative number of unique users during that period in an area chart (date as x-axis), so I'm trying:
 
users_distinctcount_acc_until_date =
VAR min_date = min(calendarTable[Date])
RETURN CALCULATE(
    [users_distinctcount],
    FILTER(
        ALL(userLogTable),
        userLogTable[date] <= max(calendarTable[Date]) &&
        userLogTable[date] >= min_date
    )
)
 
But this second function is just returning the number of users for each day, not accumulating. I could go for:
 
CALCULATE(
    [users_distinctcount],
    FILTER(
        ALL(userLogTable),
        userLogTable[date] <= max(calendarTable[Date])
    )
)
 
Then it does accumulate, but since the beginning of userLogTable, not just that week 
Need Help
Show and Tell
  • flaviorangel's avatar
    flaviorangel
    3 years ago

    Thank you! You were right about using ALLSELECTED, but your solution would still not accumulate. Here is the final solution:

    users_distinctcount_acc_until_date =
CALCULATE(
    [users_distinctcount],
    FILTER(
        ALLSELECTED(userLogTable),
        userLogTable[date] <= max(calendarTable[Date])
    )
)



     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    flaviorangel , better to use date/calendar  table joined with your table

     

    CALCULATE(
    [users_distinctcount],
    FILTER(
    ALL(Date),
    Date[date] <= max(Date[Date])
    )
    )

     

    You can also explore the window function

    Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

  • TomasAndersson's avatar
    TomasAndersson
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi!
    Try using ALLSELECTED() instead, while calculating min_date from inside the filter:

    users_distinctcount_acc_until_date =
CALCULATE(
    [users_distinctcount],
    FILTER(
        ALLSELECTED(userLogTable),
        userLogTable[date] <= max(calendarTable[Date]) &&
        userLogTable[date] >= min(calendarTable[Date])
    )
)

    Hope this helps!

    • flaviorangel's avatar
      flaviorangel
      New Member
      3 years ago

      Thank you! You were right about using ALLSELECTED, but your solution would still not accumulate. Here is the final solution:

      users_distinctcount_acc_until_date =
CALCULATE(
    [users_distinctcount],
    FILTER(
        ALLSELECTED(userLogTable),
        userLogTable[date] <= max(calendarTable[Date])
    )
)



       
      • TomasAndersson's avatar
        TomasAndersson
        Icon for Solution Sage rankSolution Sage
        3 years ago

        Ah, right. MIN is not needed there.

        Glad you managed to get it to work!