cumulative sum by date and product

[Anonymous]

hello, please help me

i am trying to make cumulative sum (running sum) table for every day in year and by product, this is the example

date	product	value
1-Jan-21	ayam	1
1-Jan-21	bebek	2
2-Jan-21	ayam	3
2-Jan-21	bebek	4
3-Jan-21	ayam	5
3-Jan-21	bebek	6

i got on internet this formula

running sum = CALCULATE( 
 SUM('Sheet1'[Value]), 
 FILTER(ALLSELECTED('Sheet1'), 
 'Sheet1'[date] >= DATE(YEAR(MAX('Sheet1'[date])),1,1) &&
 'Sheet1'[date] <= MAX('Sheet1'[date])
 ))

from that formula i got table like this

date	product	value	cumulative sum
1-Jan-21	ayam	1	3
1-Jan-21	bebek	2	3
2-Jan-21	ayam	3	10
2-Jan-21	bebek	4	10
3-Jan-21	ayam	5	21
3-Jan-21	bebek	6	21

i hope the result like this

date	product	value	cumulative sum
1-Jan-21	ayam	1	1
1-Jan-21	bebek	2	2
2-Jan-21	ayam	3	4
2-Jan-21	bebek	4	6
3-Jan-21	ayam	5	9
3-Jan-21	bebek	6	12

how to make table like this?

thanks in advance

[gomesan1]

@edhans I understand CALCULATE changes the context, but in my case I need the Cumulative values considering the context resulted from the visual/page filters. How can I do it? Thanks in advance!

[edhans · Accepted solution]

Try this @Anonymous 

Cumulative Value = 
VAR varDate = SELECTEDVALUE(Data[Date])
VAR varProduct = SELECTEDVALUE(Data[Product])
RETURN
    CALCULATE(
        [Total Value],
        Data[Date] <= varDate
            && Data[Product] = varProduct
    )

THe [Total Value] measure is just SUM(Data[Value) - I always use explicit measures in visuals, not dragging fields from the field pane.

[Anonymous]

Hi Edhans, thanks for your solution it's work 😀

but if i want start from this year how to filter the formula

i combine your formula with this formula

'Sheet1 (2)'[date] >= DATE(YEAR(MAX('Sheet1 (2)'[date])),1,1) &&
'Sheet1 (2)'[date] <= MAX('Sheet1 (2)'[date])

and then i got error "A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

_run sum = 
var vardate = SELECTEDVALUE('Sheet1 (2)'[date])
var vartype = SELECTEDVALUE('Sheet1 (2)'[Type])
return

CALCULATE(
 [sum value],
 'Sheet1 (2)'[date] <= vardate &&
 'Sheet1 (2)'[Type] = vartype &&
 'Sheet1 (2)'[date] >= DATE(YEAR(MAX('Sheet1 (2)'[date])),1,1) &&
 'Sheet1 (2)'[date] <= MAX('Sheet1 (2)'[date])
)

how solve this problem?

thanks in advance

[edhans]

This is actually very easy to do if you have a Date table. The predicate for Data[Date] removes the filter for the date.

VALUES(Date[Year]) puts it back. So you see how I modified the data to have some 2020 data for Ayam, but it resets Jan 1 for the running total. You'll also notice I replaced the Data[Date] field in the measure with 'Date'[Date] - again, using the DIM Date table.

Cumulative Value = 
VAR varDate = SELECTEDVALUE('Date'[Date])
VAR varProduct = SELECTEDVALUE(Data[Product])
RETURN
    CALCULATE(
        [Total Value],
        'Date'[Date] <= varDate,
        Data[Product] = varProduct,
        VALUES('Date'[Year])
    )

My table is out of sort order but it doesn't matter. You can sort by date and it still works.

Here is the Power Query code for a VERY simplistic date table. It just has the Date and Year.

let
    Source = {Number.From(#date(2020,1,1))..Number.From(#date(2021,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type)
in
    #"Inserted Year"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

Now, Name that Date, then when back in DAX, right-click and Mark as Date Table, selecting the Date column.
Finally, set your model to look like this. You'll notice I hid the Date in the data table. You want to use the Date from your Date table - best practice. 

I am 100% sure this can be done without a date table, but I am also 100% sure your DAX will be more complex, as you are discovering. Using a proper Star Schema with Dimension and Fact tables (Date is a dimension, your sales data or whatever it is is the FACT table) makes the DAX much easier. 

[ahp-sig]

Anyone know how to solve missing categories in the results if a category does not have any values for a specified time period? Example: Product C does not have any amounts in Feb-23 so C will be excluded completely from the displayed results

Source Data:

Result:

[Anonymous]

Hello @edhans 

I have a table containing, Date, category1 (Fund), category 2(Board), category 3(IEO), net balance 
I need a cumulative sum of the net balance for all 3 categories with a month filter in it :

I have used DAX for the cumulative sum for the month which is working using :
Cumm actual =

Var Maxd = MAX(new_shptransactionscurrentyears[new_postingdate])

Return

   CALCULATE(SUM(new_shptransactionscurrentyears[new_netbalance]),ALLSELECTED(new_shptransactionscurrentyears) ,(new_shptransactionscurrentyears[new_postingdate] <= Maxd))

& Tried 

CALCULATE(SUM(new_shptransactionscurrentyears[new_netbalance]),filter(allselected(new_shptransactionscurrentyears[new_postingdate]),new_shptransactionscurrentyears[new_postingdate] <=max(new_shptransactionscurrentyears[new_postingdate])))

This worked fine for the month table however doesn't work on the table with categories in it or with filter on 

Thank you & I really appreciate your help 
#Need Help

• Labels:

 

• Need Help

[apiwatTantipor]

Hi Community my friend

I'd like to need your help to make Table in BI to accumulate % same as the right table of excel below.