I have a date table and a table that shows customers, start date and cancel date. I want to do a by month bar chart that shows a count of customers that started in the month and cancelled in the month.
How do I limit my count to each month for the start date and cancel date. I understand how to do this if I was counting by start month only, but I a confused how I would count a customer as a START in say April and then that same customer as a CANCEL in July when both the START and CANCEL date are on the same row of data for that customer.
Hello @ScottTPA,
One way to do this is very similar to the staff calculation presented by Sam here:
You will need to establish inactive relationships between your two date columns and your calendar or date table. Once those relationships are established you can activate them within your measures when needed as described in the above link.
If you still have trouble after watching the video, perhaps you could upload your PBIX file, and a description of your desired results. Many here will be happy to assist.
I think the best way to handle this will be to connect your date table date field to your start date and your end date using inactive relationships for both. Then have two different measures, one for counting starts using USERELATIONSHIP to turn on the inactive relationship between date and start date, and the second measure counting cancellations, using USERELATIONSHIP to turn on the inactive relationship between date and end date.
I didn’t see @JBright’s post until after I posted mine, but he found the video I was originally looking for. Same general approach, just slightly different examples. If you’ve already got the start date measure working well, as it seemed you do from your initial post, once you set up the inactive relationships, you should just be able to reuse that general pattern with the additional USERELATIONSHIP statement.
Alternatively you could design a specific table for this need in Power Query just unpivot start and cancel date columns…
You could even use Group by with it’s optional parameter GroupKind.Local suitable for aggregation of time series if you only need to calculate by a fixed period like week, month or …
Hi @JBright, did the response provided by the contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!