Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

cumulative sum by date and product

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

dateproductvalue
1-Jan-21ayam1
1-Jan-21bebek2
2-Jan-21ayam3
2-Jan-21bebek4
3-Jan-21ayam5
3-Jan-21bebek6

 

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
dateproductvaluecumulative sum
1-Jan-21ayam13
1-Jan-21bebek23
2-Jan-21ayam310
2-Jan-21bebek410
3-Jan-21ayam521
3-Jan-21bebek621

 

i hope the result like this

 

dateproductvaluecumulative sum
1-Jan-21ayam11
1-Jan-21bebek22
2-Jan-21ayam34
2-Jan-21bebek46
3-Jan-21ayam59
3-Jan-21bebek612

 

how to make table like this?

 

thanks in advance

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Try this @Anonymous 

edhans_0-1626984735237.png

 

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

7 REPLIES 7
gomesan1
Frequent Visitor

@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
Super User
Super User

Try this @Anonymous 

edhans_0-1626984735237.png

 

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

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

edhans_2-1627006963958.png

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. 

edhans_1-1627006705510.png

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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:

ahpsig_0-1709847434656.png
Result:

ahpsig_1-1709847514271.png

 

 

Anonymous
Not applicable

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 :

BS097_0-1660224455190.png

 

 

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:
 

Capture.JPG

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.  

329693554_568763538510710_8565337787855421658_n.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors