topic Re: cumulative sum by date and product in DAX Commands and Tips

cumulative sum by date and product

Anonymous — Thu, 22 Jul 2021 17:23:04 GMT

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

Re: cumulative sum by date and product

edhans — Thu, 22 Jul 2021 20:15:02 GMT

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.

Re: cumulative sum by date and product

Anonymous — Fri, 23 Jul 2021 00:54:35 GMT

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

Re: cumulative sum by date and product

edhans — Fri, 23 Jul 2021 02:24:22 GMT

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.

Re: cumulative sum by date and product

gomesan1 — Fri, 20 May 2022 14:18:32 GMT

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!

Re: cumulative sum by date and product

Anonymous — Thu, 11 Aug 2022 13:28:06 GMT

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

Re: cumulative sum by date and product

Anonymous — Mon, 13 Feb 2023 08:30:39 GMT

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.

Re: cumulative sum by date and product

ahp-sig — Thu, 07 Mar 2024 21:39:12 GMT

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:

Re: cumulative sum by date and product

Anonymous — Tue, 27 Aug 2024 03:21:45 GMT

Thank you so much! Your solution worked in one of my dataset but when I wanna apply it into another dataset, the measure always returns the exact same value of the current date value, instead of the cumulative amounts for all previous dates I expected.

Here is the measure I used and the line & bar chart to demonstrate the problem (you can see the line is not for cumulated values), can you help take a look at anything wrong might go wrong here? Thanks!