Running Total "totaling" same day depreciation data

[dametreas]

Hello,

I'm seeking help on an issue with running totals counting same day remaining balance. I have searched all over with no luck on how to show depretiation in contract value through running totals.

It works perfectly except for when i run into same date data. Any help would be greatly appreciated.

Remaining Balance Duration = CALCULATE(SUM('Invoice Log'[Remaining Balance]),FILTER('Invoice Log','Invoice Log'[Invoice Date]<=MAX('Invoice Log'[Invoice Date]) && 'Invoice Log'[Invoice Date]>=MAX('Invoice Log'[Invoice Date])))

Current calculation ^^ Source date \/ \/

[v-stephen-msft]

Hi @dametreas ,

If you want cumulative values, you could create the measure like

Remaining Balance Duration = CALCULATE(SUM('Invoice Log'[Remaining Balance]),FILTER(ALLSELECTED('Invoice Log'),'Invoice Log'[Invoice Date]<=MAX('Invoice Log'[Invoice Date]) ))

If this is not your expected result, please provide your expected result.

Best Regards,

Stephen Tao

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

[Greg_Deckler]

@dametreas So what is the problem with the highlighted value? Is it twice what it should be, half of what it should be? Lower than it should be, higher than it should be?

[dametreas]

@Greg_Deckler it is totalling both remaining balances for the same day to approx 1m+ instead of showing the "lesser" amount remaining on that day which would be 511k. I believe it has to do with the "Sum" portion of the formula in the running totals. 

I need it to show the depretiation of the contract value as invoiced. But since there is two invoices on one day the running total is calculating and adding both remaining balances on that day causing the spike and reporting 1m remaining instead of 511k.

Hopefully that clears it up a bit. Thank you for the reply!! 

[Greg_Deckler]

@dametreas Can you post sample data that replicates the issue? Probably solvable.

[dametreas]

@Greg_Deckler 

Current formula in power bi - 

Total Invoiced Duration = CALCULATE(SUM('Invoice Log'[Total Invoiced To Date]),FILTER('Invoice Log','Invoice Log'[Invoice Date] <=MAX('Invoice Log'[Invoice Date]) && 'Invoice Log'[Invoice Date] >=MAX('Invoice Log'[Invoice Date])))

Data pulled from Excel - Light yellow highlighted is the same day different invoice. Orange highlighted at end is the depretiation total contract amount left

Power Bi displayed data - Showing on the 23rd instead of showing the lowest amount after the invoiced amount its adding the remaining balance from excel source data column remaining balance together for that date. 

I hope this is the information you were seeking. I appreciate your time!!