Relationship from Calender Table with Table with Start and End Date

[Contezini]

Dear Community,

I am having an issue which I believe it might be something common, since I already found a lot of similar topics searching for this topic through the community. But sadly, I couldn't find a solution that fits to my problem.

I have two Data Tables and two Head Tables:

1. Site Production Data: Table containing Site ID, Month and Energy production from that month
2. Incidents: Table contaning all the incidents: Site ID, Comment, Start Date and End Date
3. Site Info: Table contaning info from each site (address, etc)
4. Calender: An =calenderauto() table to use unique filter for Date (in daily basis)

(please see attached xlsx  to see a sample of the data used)

https://drive.google.com/file/d/1CGfA3yFGci3GuOx7bkFi912pHPW08SGE/view?usp=sharing 

On Power BI I want the user to be able to select an specific Site and Month, and then he should see:

• The Production for that Month
• All the Incidents that happened in between that month (in a table, for example)

My Problem is:

• How should I relate the dates? I tried with Calender and StartDate, but obviously that is bad, because when I filter "February", I don't get the Incidents that started in January and where only finished in February (or even in March)

A solution I tried:

1. Cancelled the relation between Calender and Incidents
2. Create a measured called "Check_If_Active", which should check if the incident was active during the filtered month

VAR xFirstPeriod= CALCULATE(FIRSTDATE(Calender[Date].[Date]),ALLSELECTED(Calender))
VAR xLastPeriod= LASTDATE(Calender[Date].[Date])

VAR xStartDate = CALCULATE(MIN(Incidents[Start Date]))
VAR xFinishDate= CALCULATE(MIN(Incidents[Finish Date])
)
RETURN
    IF(
        AND(
            xStartDate  <=xLastPeriod,
            OR(ISBLANK(xFinishDate), xFinishDate >= xFirstPeriod)
        ),
        "Active",
        "Inactive"
    )​

The idea was to put that measure in the Incidents Table and filter only the "Active". I tried changing it with ALL, ALLSELECTED, FIRSTDATE, etc. but not a single variation I tried worked.

Can someone guide me through how should I Proceed in this case?

Extra:

I would also like to make a line chart: on X axis, the Days and on Y Axis the count of Active Incidents

For example: If I have the two following Incidents:

Inc1 - Start Date 01/03/20 - Finish Date 04/03/20

Inc2 - Start Date 03/03/20 - Finish Date 05/03/20

The chart would count 2 for 3rd and 4th, and 1 for 1st, 2nd and 5th of march.

[AlexisOlson · Accepted solution]

Since the incidents can span multiple dates and you only have one row for each incident, creating a relationship to the calendar table isn't going to work in a very useful way. Note that trying to put a calculated column with "active"/"inactive" on the incident table is doomed to failure since a calculated column cannot read slicer values since it is only evaluated once per data load (not in response to user interactions).

A measure can read slicer values, so you can write one to count incidents on a particular date (to use in your chart) along these lines:

IncidentCountOnDate =
VAR xDate = SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    COUNTROWS (
        FILTER (
            Incidents,
            Incidents[Start Date] <= xDate
                && Incidents[Finish Date] >= xDate
        )
    )

Filtering incidents for a month is more difficult than for a day but your Check_If_Active seems to be on the right track (as a measure, not a calculated column) if it's filtering a visual.

[Contezini]

Thank you for your answer Alexis!

The active count measure worked, thank you!

At first instance, it wasn't working. Later on I realized I had another table with "both" relation which was the reason for the measure to not be working....

The measure I was trying to create is not even needed anymore:

I just add on "Filters for this visual" this measure you suggested to be greater then 0, and it automatically filters only the active ones in the selected period.

PS: I did a small change in the measure you sent to englobe a bigger period (like selecting multiple months)

That's how the measure looked like in the end:

3.0 Active Incidents = 
VAR xBeginDate = (FIRSTDATE( 'Calender'[Date] ))
VAR xEndDate = (LASTDATE( 'Calender'[Date] ))
VAR xCount = 
    COUNTROWS (
        FILTER (
            Incidents,
            Incidents[Start Date] <= xEndDate
            && (Incidents[Finish Date] >= xBeginDate || ISBLANK(Incidents[Finish Date]))
        )
    )
RETURN