topic Cumulative Actuals with Filtering in DAX Commands and Tips

Cumulative Actuals with Filtering

Jens1983 — Sat, 11 May 2024 18:55:54 GMT

Hi everybody,

for cumulating my actuals i used the following code and it works fine:

Cumulative Actuals = CALCULATE(SUM(Actuals[VAlue]), FILTER(All(Actuals), Actuals[Period] <= MAX(Actuals[Period])))

But the problem is, that when i want to attach filters to my visuals, they are being ignored. How do i get this flexibility? I think ithas something to do with the "All" Funktion in the code which keeps ignoring filters?

Greetings

Jens

Re: Cumulative Actuals with Filtering

nandic — Sat, 11 May 2024 22:08:47 GMT

Jens1983 function ALL removes all filters from table Actuals and then applies only period <= max period.
Everything else is not applied due to ALL function.

What you can try is to do ALL(Actuals[Period]) - so removing filters impacting only this column, not whole table.
Or using ALLSELECTED(Actuals) instead of ALL(Actuals).

Cheers,
Nemanja Andic

Re: Cumulative Actuals with Filtering

Anonymous — Tue, 14 May 2024 08:08:25 GMT

Hi Jens1983 ,

I want to validate the valuable input provided by nandic . Their initial thoughts helped guide my approach. However, I noticed that more detail is needed to fully understand the problem.

In the ALLSSELECTED calculation, only the filters inside the visual object are ignored. This is different from the way ALL behaves. From the ALL function's point of view, all filters inside or outside the visual object are ignored. More details can be found in the link : Power BI DAX: ALL vs. ALLSelected - RADACAD.

I created two MEASURES.

Measure = CALCULATE(SUM(financials[ Sales]),FILTER(ALL(financials),'financials'[Date] <= MAX('financials'[Date])))

Measure 2 = CALCULATE(SUM(financials[ Sales]),FILTER(ALLSELECTED(financials[Date]),'financials'[Date] <= MAX('financials'[Date])))

They return the same result when there is no data selected in the slicer.

When I filter in the slicer, the MEASURES remain the same results, while MEASURES 2 returns the new values. More details can be found in the pbix attachment.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.