Accumulate values throughout dates with other filter contexts

[FStettler]

Hello!

I'm trying to get a DAX formula that will accumulate counted values throughout the calendar dates in a Clustered column chart, but also taking into consideration additional filter contexts added by a Matrix and a Slicer. 

Among other items, here's the data model:

Table: BOOKINGS

Columns: Booking Date (active relationship with DATES_LOOKUP[Dates]), Accommodation Date, Room Category

Table: DATES_LOOKUP

Columns: Date (active relationship with BOOKINGS[Booking Date])

The visual I'm working on to accumulate these values is a Clustered column chart with these specifications

Y: Q of accumulated reservations (bookings)

X: DATES_LOOKUP(Dates)

However, there are 2 visuals that add filter context to Clustered column chart:

Matrix with Room Category

Date Slicer with Accommodation Date

So for a specific Room Category and Accommodation Date, I'd like the Clustered column chart to draw spikes, each one higher than the previous one, showing the accumulated quantity of reservations (or bookings) as they played out over time.

For instance:

I know that I have 5 reservations that have Accommodation Date 15/8/2020 for a specific Room Category.

1 done on 1/1/2020

1 done on 3/1/2020

3 done on 7/1/2020

The Clustered column chart should show a spike representing 1 for 1/1/2020, another one representing 2 for 3/1/2020, and another one representing 5 for 7/1/2020, and so on.

I have done the following DAX formula:

CALCULATE (

COUNT ( BOOKINGS[Accomodation Date]) ,

FILTER ( BOOKINGS , BOOKINGS[Booking Date]<=MAX(BOOKINGS[Booking Date]) ) )

But this is only showing the number of reservations done on a specific Booking date, but not accumulating them, as if the filter argument was BOOKINGS[Booking Date] = MAX(BOOKINGS[Booking Date])  and not <=.

I've also tried using "...FILTER(ALL(BOOKINGS)..." This will accumulate the values but also will completely ignore the other 2 filter contexts from the Matrix and the Slicer.

Thanks so much in advance!

Facundo

[mahoneypat]

I would try this measure instead:

CALCULATE (

COUNT ( BOOKINGS[Accomodation Date]) ,

ALL(BOOKINGS[Booking Date]), BOOKINGS[Booking Date]<=MAX(BOOKINGS[Booking Date]) ) )

[FStettler]

Hi @mahoneypat thanks a million for your swift response.

I'm afraid it didn't help much as I can't even lock it in. I get this error message:

"A single value for column 'Booking Date' in table 'BOOKINGS' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Looks like formula ALL won't take a filter context as an argument.

Thanks again.

[v-deddai1-msft]

Hi @FStettler ,

Would you please try the following measure:

Measure =

CALCULATE (

    COUNT ( BOOKINGS[Accomodation Date] ),

    FILTER (

        ALLSELECTED ( BOOKINGS ),

        BOOKINGS[Booking Date] <= MAX ( BOOKINGS[Booking Date] )

    )

)

Best Regards,

Dedmon Dai

[FStettler]

@v-deddai1-msft Hi again!

Just if you could explain why my DAX wasn't working?

I mean, as per my concern, by using FILTER I'm creating a subset of a table applying the filter conditions set out within the FILTER formula, which was "<=".

Why would it only behave as if it was "="?

But then ALLSELECTED() would get the job done?

It's still unclear to me.

Thanks so much once again.

Kind regards,

Facundo. 

[FStettler]

@v-deddai1-msft This absolutely has got the job done.

Thanks a million!

Facundo

[mahoneypat]

My bad.  I meant to just add All() to your Filter one.

CALCULATE (

COUNT ( BOOKINGS[Accomodation Date]) ,

Filter(ALL(BOOKINGS[Booking Date]), BOOKINGS[Booking Date]<=MAX(BOOKINGS[Booking Date]) ) ))