Visualization of the number of consecutive zero sales in a month's working days.

[nima_data]

Hi,

I am struggling with the following task in Power BI using DAX:

I have the following data table:

Where name refers to a list of customer names, Date is the date of the data record, workDay is the number of working days in each month (for example, 2022-07-05 is the third working day of July), and sold is 0 if the customer does not have a purchase record and 1 if the customer does.

I'd like to create the following visualization, which shows how many working days a customer has gone without making a purchase (i.e., how many days with a consecutive record of 0 in the sold column), and if he/she has made a purchase (i.e., sold=1), he/she resets and finds the first column. For example, Alian has not made a purchase on his third recorded day, so he is placed in the third column. Or, since David made a purchase in the third record, he is reset and placed in the first column.

Thanks in advance for any help you can provide.

Please find the sanitized sample data here:

https://docs.google.com/spreadsheets/d/12KnF61zAkr9XaagaRjLSskhA2rxQjWaC/edit?usp=sharing&ouid=10999...

[lbendlin · Accepted solution]

That visualization is impossible in Power BI (and probably in many other BI tools) . The best you can do is a concatenation of client names in the same cell.

Why is Sarah in column 1 and not column 0 ?