Cumulative number of event dates from multiple category columns, against a single date axis

[RichardMo]

Hi everyone,

I'm a Power BI beginner. I have a problem I'm struggling with.

I have a table like this (imported from a data warehouse).

Person                Inquiry date           Booking date                1st Appointment            2nd appointment

Person 1              1/1/23

Person 2              1/1/23

Person 3              3/1/23                    4/1/23                        5/1/23                             6/1/23

Person 4              4/1/23                    5/1/23                        5/1/23                             7/1/23

Person 5              4/1/23                    4/1/23                        6/1/23

Person 6              5/1/23                    5/1/23                        7/1/23                             9/1/23

Person 7              5/1/23                    6/1/23                        7/1/23

Person 8              6/1/23

I want to create a multi-line chart showing the cumulative number of inquiries/bookings/1st appointments etc, against a single date axis.

Initially I thought of unpivoting the table in query editor, to create a new table like this (on which I can easily do the DAX cumulative counts). 

Person           Stage             Date

Person 1        Inquiry           1/1/23

Person 2        Inquiry           1/1/23

Person 3        Inquiry           3/1/23

Person 3        Booking         4/1/23

Person 3        1st Appt         5/1/23

Person 3        2nd Appt        6/1/23

Person 4        Inquiry           4/1/23

Person 4        Booking         5/1/23

Person 4        1st App          5/1/23

Person 4        2nd App         7/1/23

However this creates a massive secondary fact table and causes me all sorts of difficulties in my model, which I won’t go into here.

Is there any way to create the cumulative chart from the first table?

Many thanks!

[amitchandak]

@RichardMo , Create a date table, Join it with all dates and create measure using userelationship for the one having inactive join , refer termination code in example

and then use this code on measure already created

example

booked = calculate( Count(Table[person]),USERELATIONSHIP ('Table'[Booking Date], 'Date'[Date]))

Cumm Sales = CALCULATE([booked ],filter(all('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm Sales = CALCULATE([booked],filter(allselected(date),date[date] <=max(date[Date])))

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Also, check

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[RichardMo]

Hi, actually it only worked for the first two, the others created circular dependencies.

[RichardMo]

Hi Amitchandak, it works perfectly. Thank you.