cancel
Showing results for
Did you mean:
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
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.

Thanks,
Angelia

7 REPLIES 7
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())```

?

Proud to be a Datanaut!

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

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

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

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.

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

Frequent Visitor

## Re: Running total limit

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.

Thanks,
Angelia

Announcements

Power BI Super User, Greg Deckler, explains

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 40 members 838 guests
Recent signins: