Forum Discussion

LaurenceSD's avatar
LaurenceSD
Icon for Advocate II rankAdvocate II
5 years ago
Solved

Cumulative Total including hidden dates

Hi,

 

I'm struggling with a cumulative total formula, to include hidden dates (dates are hidden with a filter)

 

 

At the top is the graph with no filter on it and you can see FY216 has 17k of guests, on the bottom I've added a filter to be on or after 1st Jan 2016 & the FY2016 figure reduces to 2k.

 

My formulas are 

Guests = COUNTROWS(vwGuests)
Guests Cumulative = sumx(FILTER(ALLSELECTED(Dates),Dates[Dates]<=MAX(Dates[Dates])),[Guests])
 
The strange thing is, is that I took the cumulative formula from another report I'd made in the past and it seems to be working fine on that report, but not this one. But on the other report, I was using a different filter, I had a relative date filter in place.
 
Any assistance in getting this to work is greatly appreciated + any insight into where I'm going wrong would be great too!
 
Thanks
 
Laurence

 

Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    LaurenceSD change ALLSELECTED to ALL

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

1 Reply

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    LaurenceSD change ALLSELECTED to ALL

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡