Cumulative Totals per user in a table by selected period

[Anonymous]

Hi Power BI Community ! 

I am a new Power BI and need your help in solving my solution.

In the below image, I have created a table visual which has user_id , Total Bookings and Cumulative Bookings by User.

I need to have each user's cumulative booking count from their earliest booking date to the period filtered by the slicer.

Unfortunately, my Cumulative Bookings by User is only getting the the cumulative booking of that month itself. (In this instance, when I have selected 2021 April) 

We can see that for the first user_id, user has 45 Cumulative Bookings in April 2021, but in actual fact, user's Cumulative Bookings is 57.

An example of what I wish to achieve:

(I achieved this by performing multiple selection on the Month data, which is not an ideal way to perform cumulative totals manually) This example shows the correct Cumulative Bookings by the first user_id.

Here is the code for my Cumulative Bookings by User:

Cumulative Bookings by User =
CALCULATE( COUNT('Bookings'[booking_no]),
FILTER( ALL( 'Bookings'[Booking Date] ),
'Bookings'[Booking Date] <= MAX('Bookings'[Booking Date]))
)

Appreciate any advice and solutions !

Thank you.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Because you are using a date table to filter the Bookings table, 

you will need to change the formula to

Measure = 
CALCULATE (
    COUNT ( 'Bookings'[booking_no] ),
    FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , try like

Cumulative Bookings by User =
CALCULATE( COUNT('Bookings'[booking_no]),
FILTER( ALL( 'Bookings'[Booking Date] ),
'Bookings'[Booking Date] <= MAX('Bookings'[Booking Date])
&& 'Bookings'[User_id] = MAX('Bookings'[User_id])
)
)