Removing events that have not yet happened when using running total on a line graph

[sarah2_williams]

Hi all,

Have read a few post on the same topic but don't think they are working for my issue.. I have the below graph, I want the registered for 2024 to end at June, as the others are yet to happen.. I think where I'm struggling is that the events are not formatted as date, they are text values:

using the below formula:

Registered 2024 running total in event =

CALCULATE(

    [Registered 2024],

    FILTER(

        ALLSELECTED('All Total and Region'[event]),

        ISONORAFTER('All Total and Region'[event], MAX('All Total and Region'[event]), DESC)

    )

)

Any idea how I can get the events that have not yet happend to not show?

many thanks

Sarah 🙂

[rajendraongole1 · Accepted solution]

Hi @sarah2_williams - you can try below measure as the events are there upto June.

Measure:

Registered 2024 running total in event =
VAR MaxEventDate = MAX('All Total and Region'[EventDate])
RETURN
CALCULATE(
[Registered 2024],
FILTER(
ALLSELECTED('All Total and Region'[EventDate]),
'All Total and Region'[EventDate] <= MaxEventDate &&
ISONORAFTER('All Total and Region'[EventDate], MaxEventDate, DESC)
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Anonymous]

Hi @sarah2_williams ,

Did rajendraongole1 and I's answers help you solve your problem?

If the answers helped you, please mark the answers as solutions. This will be of great help to other users who have similar problems as you.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Anonymous · Accepted solution]

Hi @sarah2_williams ,

Thanks for the reply from @rajendraongole1 .

Please try:

Modify the date column in Text format to Date format in Power Query as shown below:

Using DAX or converting the format in Desktop will report an error.

Now, try this syntax:

Registered 2024 running total in event =
CALCULATE(
    [Registered 2024], [Registered 2024], [FILTER(
    FILTER(
        ALLSELECTED('All Total and Region'), ISONORAFTER('All Total and Region'[EventDate], MAX('All Total and Region'[EventDate]), DESC) 
        &&
        'All Total and Region'[EventDate] <= TODAY()
    )
)

If that doesn't succeed in helping you, I would be grateful if you could provide me with the pbix file or sample data.

Remember to remove sensitive data and do not log in to your account in Power BI Desktop when uploading the pbix file.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[rajendraongole1 · Accepted solution]

Hi @sarah2_williams - you can try below measure as the events are there upto June.

Measure:

Registered 2024 running total in event =
VAR MaxEventDate = MAX('All Total and Region'[EventDate])
RETURN
CALCULATE(
[Registered 2024],
FILTER(
ALLSELECTED('All Total and Region'[EventDate]),
'All Total and Region'[EventDate] <= MaxEventDate &&
ISONORAFTER('All Total and Region'[EventDate], MaxEventDate, DESC)
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!