Forum Discussion

FStettler's avatar
FStettler
Icon for Helper I rankHelper I
6 years ago

Accumulate values throughout dates with other filter contexts

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
 

 

 

Need Help

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    I would try this measure instead:

     

    CALCULATE (
    COUNT ( BOOKINGS[Accomodation Date]) ,
    ALL(BOOKINGS[Booking Date]), BOOKINGS[Booking Date]<=MAX(BOOKINGS[Booking Date]) ) )
     
     
    • FStettler's avatar
      FStettler
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        v-deddai1-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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