Power BI - Check if events are between two dates in related table

1
votes

Let's say I have a table of projects and their associated companies, dates, and costs:

enter image description here

Project IDs are unique, but one company can have multiple projects. This table is linked (many to one) with a "Company" table:

enter image description here

Which is then linked (one to many) with a "Status" table:

enter image description here

I'm just using colors here, the type of status is arbitrary.

I'm looking for a way to sum up the cost of projects for each company only while the company was in "green" status. I assume it could be a calculated column on the Project table, maybe just as a True/False flag. Look over at the Status table, filter by Company, filter Status to green, and if Project[Date] is in between Start Date and End Date, return True.

In the example above, three projects would be marked True: 00001, 00006, and 00007.

What's the best way to do this in DAX?

1
datepowerbidax
also 00006 is True, since 2020-05-12 is between 2020-01-01 and 2020-10-31. What's confusing is the overlapping statuses red and green in the same periods. – sergiom
Right! Good catch, thanks. – yaxx0r

1 Answers

1
votes

Since in the sample data there are overlapping Red and Green statuses for the same company during the same period, we assume that if a Green status exists for the selected date, then the status is Green even if there also is a Red status for the same period and the same company.

A possible solution is to add a calculated column first with a boolean flag IsGreen.

This is a straightforward formula that iterates over the Status table checking the Company ID, the status and the date interval.

Since this is a calculated column, it's executed only during the refresh and it doesn't affect measures performance.

IsGreen =
VAR ProjectCompanyID = Project[Company ID]
VAR ProjectDate = Project[Date]
RETURN
    NOT ISEMPTY (
        FILTER (
            'Status',
            RELATED ( Company[Company ID] ) = ProjectCompanyID
                && 'Status'[Status] = "Green"
                && ProjectDate >= 'Status'[Start Date]
                && ProjectDate <= 'Status'[End Date]
        )
    )

Then we can implement the measure SumOfCostIfGreen using the IsGreen calculated column

SumOfCostIfGreen = 
CALCULATE ( SUMX ( Project, Project[Cost] ), Project[IsGreen] )

This is the resulting table visual

table visual