Need to accommodate date slicer in cumulative total measure

[Megz]

Hi There,

I started off with only one year of data and the following measure works to calculate a reverse cumulative total showing how sales rise as the time remaining to sell (selling time weeks) decreases.

cumulative sales 2 =

CALCULATE(

  COUNTA('GS_OE_Bookings'[sales_time_weeks]),

  FILTER(

    ALLSELECTED(GS_OE_Bookings),

    GS_OE_Bookings[sales_time_weeks] >= MIN(GS_OE_Bookings[sales_time_weeks])

  )

)

Now I have added data spanning a number of years, along with a calendar table which has the relationship 'Course date'[Date] / GS_OE_Bookings[start_date].  I also now have a slicer slicing on 'Course date'[Year]. The code above still works when all the years available are selected in the slicer.....

However when I select a year in the slicer, the cumulative total still kind of works but there are records hanging around that aren't relevant to the year selected...

How can I change the measure to prevent these extra records showing up?  

Any help greatly appreciated.

[Anonymous · Accepted solution]

HI @Megz,

Did the filter source field also include in your table? If that is the case, you may need to create a new table as source of slicer or use ALL function to replace ALLSELECTED to break the current relationship. (power bi use AND logic to link different filters)

Then you can manually apply filter condition in expression that extract from the filter selection.

Regards,

Xiaoxin Sheng

[Megz]

Hi Xiaoxin - Thanks for taking a look

I will take a look at your suggestion - I'm on vacation for a week or so but I'll be back on this.

I've been reading this as well but haven't got round to trying it. https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/The-Complex-Selector/td-p/1116633 

[Anonymous]

Hi @Megz,

OK, you can feel free to post if you faced any issue about this scenario.
Regards,

Xiaoxin Sheng