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 ent...
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])
    )
)



     
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