like for like and variable to show sales only for customers open at start/end of slicer

[po]

Hi,

Have following scenario.

Sales table with sales for customer and some customers closed during covid pandemic but some have remained open.

Also have a table which tells us date, customer no and open_status.

This linked into calendar date table.

We have slicer on report to allow user to select dates interested in

Want to have following

1) sum(sales) for customers who were closed between dates chosen in slicer this will be a continous period.

e.g if closed march until june

2) sum(sales) customers open throughout 

sales period will be january until august 

e.g will plot these on graph showing sales for all customers and allows comparison with sales for customers who were closed to measure relative difference in performance between these 2 sets of customers.

How can we best achieve this in power bi part 1 above.

Thanks

[Anonymous]

Hi @po ,

Do we have date column in sales table? Can you show some sample data?

Best Regards,

Jay

[po]

Hi,

yes have date in sales table.

sample data

Date            Customer  sales qty

01/06/2020  bill          10

02/06/2020  bill           5

03/06/2020  bill          2

01/06/2020 tom           3

02/06/2020 tom           2

03/06/2020 tom           4

03/06/2020  jack         7

if user selects date slicer and chooses say 01/06/2020 to 03/06/2020 want to have one variabe which shows customers who  been open throughout eg. open on 01, 02 and 03 in above example woudl show sales bill and tom.

Want another variable which shows customers who were'nt open throughout i.e. jack in above example.

Thanks

[Anonymous]

Hi @po ,

Inactive the relationship between two tables and create a measure like below.

open/close = IF(SELECTEDVALUE('Table'[Date])>=MIN('Date'[Date])&&SELECTEDVALUE('Table'[Date])<=MAX('Date'[Date]),1,0)

 Add this measure to one visual filter value = 1and to another visual filter value =0.

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[po]

Hi,

Thanks for reply.

Do you have the example .pbix the screenshots associated with?

Thanks

[Anonymous · Accepted solution]

Hi @po ,

Pbix as attached.

[po]

Hi,

Thanks again.