cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moseshewetson Frequent Visitor
Frequent Visitor

Running total limit

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!!!!

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Running total limit

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.

1.PNG

Thanks,
Angelia

7 REPLIES 7
Super User
Super User

Re: Running total limit

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

 

?

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


moseshewetson Frequent Visitor
Frequent Visitor

Re: Running total limit


@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...

 

Untitled picture.png

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

v-huizhn-msft Super Contributor
Super Contributor

Re: Running total limit

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 Frequent Visitor
Frequent Visitor

Re: Running total limit

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 Super Contributor
Super Contributor

Re: Running total limit

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 Frequent Visitor
Frequent Visitor

Re: Running total limit

v-huizhn-msft Super Contributor
Super Contributor

Re: Running total limit

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.

1.PNG

Thanks,
Angelia

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 40 members 838 guests
Please welcome our newest community members: