Running total limit

[moseshewetson]

Hello, I have a live running total for the month from our accounting system which i am having trouble with.

Here is the current formula:

Running total = 
IF(
	MIN('Date'[Date]) <= CALCULATE(MAX(DR_INVLINES[TRANSDATE]), ALL(DR_INVLINES)),
CALCULATE(
	SUM('DR_INVLINES'[Total (NZD)]),
	FILTER(
		ALLSELECTED('Date'[Date]),
		ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC) && 
			MONTH('Date'[Date]) = MONTH(TODAY()) && 
			YEAR('Date'[Date]) = YEAR(TODAY())
			
	)
)
)

And the result:

My problem is that invoices are often done in advance so that (as you can see in the picture) there is values past today's date. This is due to MAX('date'date). Can someone help to modify the formula so it only displays values less than or equal to today's date?

Everything I have tried results in a flat running total line or the line continuing on to those dates but just showing no increase.

Thanks!!!!

[Greg_Deckler]

		ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC) && 
			MONTH('Date'[Date]) = MONTH(TODAY()) && 
			YEAR('Date'[Date]) = YEAR(TODAY()) &&
                        DAY('Date'[Date]) <= DAY(TODAY())

?

[moseshewetson]

---

@smoupre wrote:

		ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC) && 
			MONTH('Date'[Date]) = MONTH(TODAY()) && 
			YEAR('Date'[Date]) = YEAR(TODAY()) &&
                        DAY('Date'[Date]) <= DAY(TODAY())

?

---

Nope Tried that...

It still includes the dates but just shows no values...

[v-huizhn-msft]

Hi @moseshewetson,

In the screenshot, what's the means of the dotted line, hows the date? And could you please share more details for further analysis?

Thanks,
Angelia

[moseshewetson]

Hello @v-huizhn-msft

The dotted line is just a trend line.

'Date' is a date table linked to a transaction table ('DR_INVLINES') that contains all of the sales transactions for the company.

I need a way to filter out all transactions that are ahead of today's date so the running total line does not show dates ahead of time.

Thanks.

[v-huizhn-msft]

Hi @moseshewetson,

Got it. Do you mind share some sample data, or .pbix file for further analysis. So we can post solution which is close to your requirement.

Thanks,
Angelia

[moseshewetson]

Hello, here is a sample 🙂

https://motuekalumber-my.sharepoint.com/personal/moses_hewetson_mlcgroup_co_nz/_layouts/15/guestacce...

Thanks,

[v-huizhn-msft · Accepted solution]

Hi @moseshewetson,

From your file, you have created the cline chart, which displays the data and running total before today. Based on your description :"Can someone help to modify the formula so it only displays values less than or equal to today's date?", I think you have got your target. While for "I need a way to filter out all transactions that are ahead of today's date so the running total line does not show dates ahead of time.", you does not show datea before today? It's confusing.

You also can create your measure using the following formula.

Running totalTest1 = 
IF(FIRSTNONBLANK('Date'[Date],1)<TODAY(),CALCULATE(
 SUM(Sales[Amount]),
 FILTER(
 
  ALL('Date'), 'Date'[Date]<=MAX('Date'[Date])
)))

Please see the following formula, it only shows data and running total before today.



Thanks,
Angelia