Forum Discussion

Jens1983's avatar
Jens1983
Regular Visitor
2 years ago
Solved

Cumulative Actuals with Filtering

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[Pe...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

nandic's avatar
nandic
Icon for Resident Rockstar rankResident Rockstar
2 years ago

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