Running Total?

[kbarber]

Hello,

I think I need help with calculating a running total, though I'm not sure.  Apologies if this has been answered elsewhere.

I took some time to create an example file.

In the example file, I have two excel tables loaded into Power Query as connection only, and loaded to data model.  I have not created any relationships or measures. 

The first table shows employees and their FTE per position. 

The second table is a list of dates incremented by 1 day.

I created a dummy end goal sheet (3rd tab).  I'd like the user to be able to filter by employee and see a line chart of the running total(?) of FTE.  Yes I'm using Excel... I hope it's clear what I'm trying to do!

I'm more of a power query guy than a DAX guy, and I think I need a DAX measure to achieve what I want...

Any help would be appreciated !!

- Kurt

[v-xuding-msft · Accepted solution]

Hi @kbarber ,

I create a measure using SUMX function. You could reference it to have a try.

Measure =
SUMX (
    FILTER (
        FTE,
        FTE[Pos Start] <= MAX ( 'Calendar'[Date] )
            && (
                FTE[Pos End] >= MAX ( 'Calendar'[Date] )
                    || ISBLANK ( FTE[Pos End] )
            )
    ),
    FTE[FTE]
)

 

Best Regards,

Xue Ding

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

[Anonymous]

We are having a peculiar situation where we need to calculating cumulative running totals in fly reducing a certain percentage value(.98). The new row should take the previous calculated value to cumulation.
Below is the sample data set. Here you see 5th row is cumulation till 4th row multiplied by .98, where 6th row should take the result of previous calculation that is 9.8 for cumulation and not 10.

Please let me know your thoughts on this which is our last resort 😊

Rep Month/Year Measure  Cumulative result Calculation
Rep1 2017001        0            0 
Rep1 2017002        0            0 
Rep1 2017003        0            0 
Rep1 2017004       10           9.8                        (0+10)*.98 = 9.8
Rep1 2017005       10          19.404                        (9.8 + 10)* .98
Rep1 2017006        0          19.01                        (19.04+0)*.98
Rep1 2017007       10          28.43                        (19.01 + 10)*.98
Rep1 2017008       20          47.46                        (28.43+20) * .98
Rep1 2017009        6          52.39                        (47.46+6)*.98
Rep1 2017010       30  
Rep1 2017011        0  
Rep1 2017012        0  

[kbarber]

hello @Anonymous  - since your use case is different, i'd recommend posting as a new question.  Also, i find if i create a dummy file and link to the post it's more likely to get a response...

- Kurt

[Anonymous]

@kbarber thanks for you reply ,i need solution for this one ,I have tried may ways but multiplication is not happening during running total values.please let me know if you get any solution for that.

[kbarber]

@v-xuding-msft  - that worked!!!

thank you so much for taking the time to respond; made my day!

In my limited understanding of DAX/Modeling, I thought I'd first have to create table relationships between the tables.  I'm a bit surprised that no relationships are necessary...

thanks again,

- Kurt

[Greg_Deckler]

I would start with the Running Total Quick Measure built into Power BI Desktop. Even if you are using Excel, you can fire up Power BI Desktop and check out the DAX code. Failing that, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...