Running totals showing in my graph after today

[jfox61]

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?

[Greg_Deckler]

@jfox61 Add this to your measure:

RETURN

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

[jfox61]

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

[jfox61]

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

        )

    )

)

[Greg_Deckler]

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

[jfox61]

Oh my gosh thank you for coming back!

So I added:

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

[Greg_Deckler]

@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

[jfox61]

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. 

[jfox61]

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.

[jfox61]

This is my date setup