Multiple Dates in same table

Question

How do I structure a dataset when I have a table has both an Activation Date and a Cancelation date (when/if an account is disconnected). How do I tie that to a Date Table that I can create visuals on.

This question is related to the following Learning Module

Answer 1

Hi Carnabuci, Michael P,

Thank you for reaching out to us on the Microsoft Q&A forum.

To analyze both Activation Date and Cancellation Date in a single table, set up the following in Power BI:

1. Date Table: Ensure a Date table with continuous dates exists, covering both activation and cancellation periods.
2. Relationships: Create an active relationship between Activation Date and the Date table, and an inactive relationship with Cancellation Date. This allows you to default to activation dates but still access cancellation dates as needed.
3. DAX Measures: By default, all measures will work based on activation dates. If you need a measure to use cancellation dates instead, apply USERELATIONSHIP in DAX to activate the cancellation date relationship as needed.

If you found the information helpful, we would greatly appreciate it if you could acknowledge it by selecting the Accept Answer & Upvote options.

Thank you.