Running total of two columns by date

[mikesdunbar]

I'm trying to calculate a running total between two columns, but to change which column to sum based on today's date. Here's my table.

What I'd like to figure out how to calculate would be that "Running Total" column I have there. It cumulatively sums from the "Approved" column up until 1/7/2023, and then continues that cumulative sum, but switched over to the "Estimated" column after 1/7/2023. In case this gets read at a later date, Today = 1/11/2023.

Thank you!

[bolfri · Accepted solution]

You can create a new column with CorrectValue and put the logic you want here:

CorrectValue = IF(fact_table[Approved]=0,fact_table[Estimated],fact_table[Approved])

What I see in your sample data: show me estimated value if I dont have any approved one.

Then a measure for RT is like that:

RT =
var last_date = LASTDATE(fact_table[Date])
return CALCULATE(SUM(fact_table[CorrectValue]),FILTER(ALLSELECTED(fact_table),fact_table[Date]<=last_date))

I hope this will help you 🙂

[mikesdunbar]

That looks like it does it. I was wondering about calculated columns. When I first started learning Power BI, I remember reading somethign that said you should go with a Measure before creating a caluclated column since calculated columns take up more space, or something like that. Is that accurate?

[bolfri]

Hmmm not really. I mean, yes and no. It alaways depends on what do you want to achive. In many scenario put whole logic into single column and using it in simple measure has better performance than writing complex measure that takes a lot of time to calculte (or even worse - when you want to filter something and need to wait few seconds to see the results). In this case calculated column is better.

[mikesdunbar]

That makes sense, thanks for your help!

[sevenhills]

Try this measure:

Running Total = 
CALCULATE(
     sum( TableApprovedEstimated[Approved]), 
     filter ( allselected(TableApprovedEstimated), 
            TableApprovedEstimated[Job Code] = Max(TableApprovedEstimated[Job Code]) 
            && TableApprovedEstimated[Date] <= Max(TableApprovedEstimated[Date]) 
    )
)
+ 
CALCULATE(
     sum( TableApprovedEstimated[Estimated]), 
     filter ( allselected(TableApprovedEstimated), 
            TableApprovedEstimated[Job Code] = Max(TableApprovedEstimated[Job Code]) 
            && TableApprovedEstimated[Date] <= Max(TableApprovedEstimated[Date])
             && TableApprovedEstimated[Approved] = 0
    )
)

Tip: If this is a 1 Million rows, it is better to add as a column "Running Total" either in power query or DAX. This kind of semi-additive calculations are always performance hit for huge set of rows.