DAX Formula – Dynamic count calcs for left outer join

[Pawel P]

Hi All and welcome to the forum,


I’m looking for help in writing the formula for the below business case.

First, let me give you an overview of the dataset and what I need to calculate.

Data

Data set is a table that is a result of appending hundreds of reports.
There are two relevant columns for the below calcs:

- ReportDate – working day formatted as dates, shows the data source (report)

- BreakID:

- BreakID is unique but only on the report level

- The same BreakID can be visible over a few dates untill it is resolved

The result I’m looking for (in the example below it's "6" which should be displayed for 03 Feb)

I would like to calculate how many BreakID were „cleared” on the given day.

This means that I need to compare the list of BreakID for let’s say today (T0) and for the previous day (T-1).

The count of breaks that were present yesterday but are no longer in today's report would be the number I’m looking for.



This is why I thought about the left outer join. I don’t know how to create DAX formula which will calculate that and allow me to visualize the data in time on the graph.



I would appreciate your help.