Power BI date and measure calculation

Question

I have a data table in Power BI which has 2 date columns: Date Created, Date Updated. I have a measure of cases which is just a count of cases. I want to create a line graph in which I want to show all the cases created, and all the cases closed but with a common date axis. My problem is if I use Date created as X-axis I get incorrect values for Cases closed as it depends on Date Updated and vice versa. My one solution is to split the graph into two different KPIs, but I wondered if there is a way to create a custom date column/table with the dates from both date created and date updated and show both the measures on that date column. I want the two measures in the image below in the same date axis.

Answer 1

The best solution that you can use is create a CalendarTable (if you dont have one); Then make relationship between your Source Table to Calendar twice. First for CreatedDate Second for ClosedDate. Change one of this two to inactive.

Next in your measure reactivate this relation.

In the chart, use the time from the calendar table.

exampleMeasure = calculate( count(SourceTable[SomeColumn]), USERELATIONSHIP('Calendar'[issue_date], SourceTable[CloseDate]))

https://dax.guide/userelationship/