Sum columns not working

[1125NEWBIUSER]

Hi,

I have got the hardest part of my DAX formulas working to get my running totals, now I want to add the 4 calculated fields I created together. I did a sumx formula, in picture below but it is not totalling correctly. If you manually add, you should have -149 but I am getting the value of 111 from the field. What did I do wrong here?

Second question, I have running totals and I want to keep this life to date, independent of date filters. Meaning if I just grabbed todays date in filter, I want the totals to remain the same. But currently, it only calculates based on the dates I am pulling into table. So i cannot just see the RT (Running Total) Orders at a point in time. Is this possible? The formula for one below.

RT Orders =

Var LastSaleDate = Calculate( LastDate( 'orders/wip'[Order date]),ALL('Orders/WIP'[Quantity]))

RETURN

IF( SELECTEDVALUE( 'orders/wip'[Order date]) > LastSaleDate, BLANK(),

    -CALCULATE( sum('Orders/WIP'[Quantity]) ,

        FILTER( ALLSELECTED( 'Orders/WIP'),

            'orders/wip'[Order date] <= MAX ('orders/wip'[Order date]) && 'Orders/WIP'[Invoiced] <>"Return"

    && 'Orders/WIP'[SaleTransType] = "Standard")))

Thanks! Chris

[Greg_Deckler · Accepted solution]

@1125NEWBIUSER If you want to add four fields together, just add them together, no need for a DAX function:

[Measure] + [Measure 1] + [Measure 2] + [Measure 3]

Same thing for columns.

I feel like you are overcomplicating your running total. I just posted a Better Running Total that demonstrates a super simple approach to this. https://community.powerbi.com/t5/Quick-Measures-Gallery/Better-Running-Total/td-p/2755666

Would need sample data and expected output to go further.

[1125NEWBIUSER]

@Greg_Deckler for the first, that did the trick, thank you very much.

For the second item, that worked as well. I added my additional features to VAR __Table and it matches the other formula I used. Is the below enough for the data set example? Below is Table 1 (Left), I have the RT versus the orders per day. Table 2 (Right), I have same thing but filtered to just see this month. The running total adjusts to filtered date of what I want to show in chart versus I want the formula to be static so I can see the left results but at a period of time.

Thanks, Chris

[Greg_Deckler]

@1125NEWBIUSER Really need the source data as text in a table so that it can be copied and pasted into an Enter Data query. I don't like to do that much typing.

First check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[1125NEWBIUSER]

@Greg_DecklerI believe I have added the based data/table as needed now. Also show examples of what I am currently getting versus what I hope to achieve.

What I have:

Order Date	Quantity
3/1/2019	150
12/1/2020	500
5/1/2021	100
9/1/2022	450

What I want is a running total, current formula used:

Better RT Orders =

    VAR __Date = MAX('Orders/WIP'[Order date])

    VAR __Table = FILTER(ALLSELECTED('Orders/WIP'),[Order date] <= __Date && [Invoiced] <> "Return" && [SaleTransType] = "Standard")

RETURN

    (- SUMX(__Table,[Quantity]))

What I get:

Order Date	Quantity
3/1/2019	150
12/1/2020	650
5/1/2021	750
9/1/2022	1200

My current formula is doing this. But it is dynamic. So when I then put into a visual, lets say I only want to see this month, I currently see:

Order Date	Quantity
9/1/2022	450

Because the visual is filtered by date, the formula is updating to only show me running total for this month. What i want to see is:

Order Date	Quantity
9/1/2022	1200

Is it possible to update my DAX so it calculates based on the total tables, not just on the visual filters that currently are driving these totals. I want to have a life to date running total that I can plug in to see at any one point in time.

Thanks, Chris