Calculate running total on a measure

[Anonymous]

Hi,

I have a measure "ranked" that creates a numbered list starting at 1, based off the index in the source table. I can't use the index itself in the visual I need to filter by an item, so the index would be all over the place as i have many different items

Ranked = RANKX( ALLSELECTED( Table1 ) , CALCULATE( SUM( Table1[Index] ) ),,ASC,DENSE )

 When I try to calculate the SUM of PLANNED_QTY referencing the "Ranked" measure, this is the DAX expression I used:

COLUMN1 = CALCULATE(SUM('Table1'[PLANNED_QTY]),FILTER('Table1','Table1'[Ranked]<=EARLIER('Table1'[Ranked])))

The error message I get says the first argument of EARLIER/EARLIEST is not a valid column reference. From what I can tell, EARLIER does not work on a measure.

I also cannot filter by the date column as there are multiple instances of the same date, that is why I needed a list of 1-100+ using the rankx function

Is there any other way to calculate a running total on the PLANNED_QTY?

[Stachu]

I don't really get the requirement here, can you share sample data? below is the post that guides on how to structure posts most efficiently
How to Get Your Question Answered Quickly 

as far as I can understand you need the sum of planned quanitity per item, correct? I don't really get the constraint on the use of date and why exactly the index/rank are needed, but that should become more clear once I see the data

[Anonymous]

Here is my data

Index, PLANNED_QTY and Date are columns, Ranked is the RANKX measure (based on Index when filtered by item)

Index      Item      Ranked      Date     PLANNED_QTY

313         A            1              6/5/19    -100

398         A            2              6/6/19    -200

409         A            3              6/6/19     500

What I need it to look like:

Index      Item          Ranked      Date     PLANNED_QTY    Running Total

313         A               1               6/5/19    -100                    -100

398         A               2               6/6/19    -200                    -300

409         A               3               6/6/19     500                      200

[Mariusz · Accepted solution]

HI @Anonymous 

You can use the below code

Sales Running Total 2 = 
CALCULATE(
    SUM(Table1[PLANNED_QTY]),
    FILTER(
       ALLSELECTED(Table1),
       Table1[Index] <= MAX( Table1[Index] )
    )
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Stachu]

these tables are the output, correct? how do the input tables look like?

[Anonymous]

Refer this video, hope this solve your challenge

https://www.youtube.com/watch?v=f5k

4fD1YJrQ