Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nima_data
Frequent Visitor

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

Hi,

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

I have the following data table:

data.jpg


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.

 

visual.jpg


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...

 

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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 ?

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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 ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.