cancel
Showing results for
Did you mean:
Frequent Visitor

Running totals showing in my graph after today

I have a graph:

It seems like this should be so easy, maybe a filter on my graph? I have researched quite a bit and have found no easy way to do this. Can you please help?

9 REPLIES 9
Super User

RETURN

IF( MAX('Date'[Date] > TODAY(), BLANK(), __Result)

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

So I tried to create a new measure:

Before Today = RETURN IF( MAX('Registrations'[Date] <= TODAY(), BLANK(), __Result))

and am getting a syntax error:
The syntax for 'RETURN' is incorrect. (DAX(RETURN IF( MAX('Registrations'[Date] <= TODAY(), BLANK(), __Result)))).

Which I should be able to figure out but ......
Frequent Visitor

Hi Greg,

Thank you and now another question, do I add this:

RETURN

IF( MAX('Date'[Date] > TODAY(), BLANK(), __Result)

to my running total measure? If so where, at the end? I know this is annoying very beginner-ish!

Count of Unit running total in Month =
CALCULATE(
COUNTA('Registrations'[Unit]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Registrations',
'Registrations'[Date].[MonthNo],
'Registrations'[Date].[Month]
),
ALLSELECTED('Registrations')
),
ISONORAFTER(
'Registrations'[Date].[MonthNo], MAX('Registrations'[Date].[MonthNo]), DESC,
'Registrations'[Date].[Month], MAX('Registrations'[Date].[Month]), DESC
)
)
)
Super User

@jfox61 Yes, like this:

``````Count of Unit running total in Month =
VAR __Result =
CALCULATE(
COUNTA('Registrations'[Unit]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Registrations',
'Registrations'[Date].[MonthNo],
'Registrations'[Date].[Month]
),
ALLSELECTED('Registrations')
),
ISONORAFTER(
'Registrations'[Date].[MonthNo], MAX('Registrations'[Date].[MonthNo]), DESC,
'Registrations'[Date].[Month], MAX('Registrations'[Date].[Month]), DESC
)
)
)
RETURN
IF( MAX('Date'[Date] > TODAY(), BLANK(), __Result)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Oh my gosh thank you for coming back!

Count of Unit running total in Month =
CALCULATE(
COUNTA('Registrations'[Unit]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Registrations',
'Registrations'[Date].[MonthNo],
'Registrations'[Date].[Month]
),
ALLSELECTED('Registrations')
),
ISONORAFTER(
'Registrations'[Date].[MonthNo], MAX('Registrations'[Date].[MonthNo]), DESC,
'Registrations'[Date].[Month], MAX('Registrations'[Date].[Month]), DESC

)
)
) RETURN
IF(MAX('Date'[Date] > TODAY(), BLANK(),__Result)

Didn't work

So I changed the statement to
RETURN
IF(MAX('Date'[Month] > TODAY(), BLANK(),__Result) - at the end

And then finally below
Count of Unit running total in Month =
CALCULATE(
COUNTA('Registrations'[Unit]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Registrations',
'Registrations'[Date].[MonthNo],
'Registrations'[Date].[Month]
),
ALLSELECTED('Registrations')
),
ISONORAFTER(
'Registrations'[Date].[MonthNo], MAX('Registrations'[Date].[MonthNo]), DESC,
'Registrations'[Date].[Month], MAX('Registrations'[Date].[Month]), DESC

)
)
) RETURN
IF(MAX('Date'[MonthNo] > TODAY(), BLANK(),__Result)

And I'm getting big fat syntax errors. I have tried multiple things, nothing works.

Thank you, thank you
Super User

@jfox61 You're missing your VAR definition. To use a RETURN statement you need a VAR statement. Check the code I posted again. It's the second line of the code. If you really want to (although I highly recommend against it) you could do it without the VAR and RETURN. But you really should use VAR's because they help break up your code into manageable chunks that you can debug using things like TOCSV, etc.

``````Count of Unit running total in Month =
IF( MAX('Date'[Date] > TODAY(), BLANK(),
CALCULATE(
COUNTA('Registrations'[Unit]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Registrations',
'Registrations'[Date].[MonthNo],
'Registrations'[Date].[Month]
),
ALLSELECTED('Registrations')
),
ISONORAFTER(
'Registrations'[Date].[MonthNo], MAX('Registrations'[Date].[MonthNo]), DESC,
'Registrations'[Date].[Month], MAX('Registrations'[Date].[Month]), DESC
)
)
)
)``````

Another thing, there is far easier syntax to create a running total: Better Running Total - Microsoft Power BI Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Hi Greg,

I'm really trying here, I swear.

I tried your simpler version to no avail. I got syntax errors there too so it's something that I am doing....

I tried this code:

Count of Unit running total in Month =
VAR _RESULT =
CALCULATE(
COUNTA('Registrations'[Unit]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Registrations',
'Registrations'[Date].[MonthNo],
'Registrations'[Date].[Month]
),
ALLSELECTED('Registrations')
),
ISONORAFTER(
'Registrations'[Date].[MonthNo], MAX('Registrations'[Date].[MonthNo]), DESC,
'Registrations'[Date].[Month], MAX('Registrations'[Date].[Month]), DESC
)
)
)
RETURN IF( MAX('Date'[Date] > TODAY(), BLANK(), _RESULT))

in the DAX Formatter on-line, it points to the parens by Blank.. well here is a screenshot:

The syntax error I get in Power BI says there are too many arguments.

Frequent Visitor

Thank you for the information sorry I didn't read your code in full! I am still getting a syntax error that says "Too many arguments were passed to the MAX function. The maximum argument count for the function is 2."

My Code

Count of Unit running total in Month = VAR _RESULT = CALCULATE(
COUNTA('Registrations'[Unit]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Registrations',
'Registrations'[Date].[MonthNo],
'Registrations'[Date].[Month]
),
ALLSELECTED('Registrations')
),
ISONORAFTER(
'Registrations'[Date].[MonthNo], MAX('Registrations'[Date].[MonthNo]), DESC,
'Registrations'[Date].[Month], MAX('Registrations'[Date].[Month]), DESC

)
)
) RETURN  IF( MAX('Date'[Date] > TODAY(), BLANK(), _RESULT))

I will look at the video you sent me, thank you. This code came from using the quick measure for running total.
Frequent Visitor

This is my date setup

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors