Cumulative sum by Item and Date

[FGGal]

Hello all,

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?.

Thank you very much for your help.

[dedelman_clng]

Any visualization that you are using should be giving you an implicit filter on user and should break down the measure as needed.  Try a table or matrix or column/bar chart (for the latter, with date as the the X-axis and user as the category).

Hope this helps

David

[dkay84_PowerBI]

When you say "up to a certain date" do you mean that that date would be chosen by a user (i.e. a slicer) and the cumulative total will reflect this chosen date?

[FGGal]

That is. I would like to know how many request have been made by a certain user when up to a date (which could be selected through a slicer, a play axis in a scatter chart, etc...)

[dkay84_PowerBI]

So the ALL() argument in the FILTER() argument makes your measure ignore any slicers/filtering, so try removing that and see if it works.

[FGGal]

I think I may have not explained myself very well. I would like to have a cumulative sum taking into consideration date and username.

I have found in StackOverflow a working solution in SQL (http://stackoverflow.com/questions/17971988/sql-server-cumulative-sum-by-group😞

select dummy_id, date_registered, item_id, quantity, price,
       (select sum(quantity)
        from t t2
        where t2.item_id = t.item_id and
              t2.date_registered <= t.date_registered
       ) as cumulative
from table t;

Is there any way to translate this to DAX? Unfortunately, I think this problem exceedes my set of skills (I'm a beginner in DAX/Power BI)

Thank you very much.

[dkay84_PowerBI]

So I recommend starting off with a table or matrix visual, putting username and date for the rows (in that order) and your cumulative sum measure as the value.  This will give you, for each username, the running total for your dates.  Then, add a slicer for date, and make sure your cumulative total matrix filters appropriately using the slicer.  Finally, if all works fine, you can remove the dates field from your matrix and just use the slicer.  The matrix (or any other visualization where username is an axis or legend) will show the cumulative total for each username up to the date selected by the slicer.

With all that said, there may need to be some tweaks to the measure, as I mentioned before, so that the ALL argument doesn't override the slicer.  If you have some sample data we can give better guidance.