Power BI date and measure calculation

user_6901319 0
Viewed 478

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.

enter image description here

dax powerbi
1 Answers

The best solution is to create a CalendarTable (if you don't have one). Then, create two relationships from your Source Table to the Calendar one :

  • First for CreatedDate,
  • Second for ClosedDate.

Switch one of these relationships to inactive. Next, in your measure reactivate it.

Finally, in the chart, use the time from the CalendarTable.

exampleMeasure = CALCULATE(
  COUNT(SourceTable[SomeColumn]), 
  USERELATIONSHIP(
    'Calendar'[issue_date],
     SourceTable[CloseDate]
  )
)

https://dax.guide/userelationship/

user_143665380
Related