Cumulative total by group in Power BI (DAX)

1
votes

After googling for two pages, I'm struggling to find a simple way to create a cumulative sum measure by date and item in Power BI (using DAX). I have a table which contains:

  • Username
  • Date (DD-MM-YYYY)
  • Number of requests in that day

I have managed to obtain the cumulative sum by using the following expression (extracted from DAXPatterns):

CALCULATE (
SUM ( Table[Requests] ),
FILTER (
    ALL ( 'Date'[Date] ),
    'Date'[Date] <= MAX ( 'Date'[Date] )
)
)

But I would like to obtain a measure indicating how many requests have been made by a user up to a certain date.

Is there a simple way to do this?

1
powerbidax
That measure looks right. If you build a report and include it and filter to user1 and 2016-12-31 you should get the requests by that user up to and including 2016. Or put date on rows and you should see the cumulative total more clearly. – GregGalloway

1 Answers

2
votes

Create calculated table using SUMMARIZECOLUMNS function and apply filter that you need on the top of that calculated table.

YourCalcTableName =
SUMMARIZECOLUMNS (
    'UsernameTable'[Username],
    'Date'[Date],
    "Number Of Requests", SUMX ( 'UsernameTable', 'UsernameTable'[NumberOfRequests] )
)

This calculated table essencialy produces 3 column table with user name, date and number of requests sent by this user on this date.

SUMMARIZECOLUMNS