Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!!
Solved! Go to 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
ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC) && MONTH('Date'[Date]) = MONTH(TODAY()) && YEAR('Date'[Date]) = YEAR(TODAY()) &&
DAY('Date'[Date]) <= DAY(TODAY())
?
@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...
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
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.
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
Hello, here is a sample 🙂
Thanks,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |