Line Chart of Unrelated Tables

[Eesa]

Hi all, 

I would like to get a help in plotting a line chart of a Table that has StartedOn date and EndedON date.

What I want is like a visual below where on my y-axis is duration and on x-axis is date. I have another date table but it is not linked to my table. I found a help in this community ( Calculate Dynamically Change Duration - Microsoft Power BI Community  ) to calculated the duration very accurately. It works very will with card visual as I play with a slicer but it can't work with line chart as it is not related. 

If I make a relation and connect date from CalendarDIM table to my PantDowntime[Started On] it will mess everything as it will filter with start date and calculate the duration regardless of the end date and it will also ignore the event started before my day and continues to this day. 

So all I want is to visualize the duration per day like this visual below and as I mentioned the measure works fine with card for single day and shows straight line for line visual

Here is the measure that calculate the duration between any selected two dates using a slicer. 

Total Downtime = 

var sdate = MINX(ALLSELECTED(CalendarDIM), CalendarDIM[Date]) 

var endate = MAXX(ALLSELECTED(CalendarDIM), CalendarDIM[Date]) + 1

return sumx(PlantDowntime, 

IF(PlantDowntime[Started On] >= sdate && PlantDowntime[Started On] <= endate && PlantDowntime[Ended On] <= endate , DATEDIFF(PlantDowntime[Started On],PlantDowntime[Ended On],MINUTE),

IF(PlantDowntime[Started On] >= sdate && PlantDowntime[Started On] <= endate, DATEDIFF(endate,PlantDowntime[Started On],MINUTE)*(-1), 

IF(PlantDowntime[Ended On] >= sdate && PlantDowntime[Ended On] <= endate && PlantDowntime[Started On] >= sdate, DATEDIFF(PlantDowntime[Started On],PlantDowntime[Ended On],MINUTE), 

IF(PlantDowntime[Ended On] >= sdate && PlantDowntime[Ended On] <= endate, DATEDIFF(sdate, PlantDowntime[Ended On],MINUTE), 

IF(PlantDowntime[Started On] <= sdate && PlantDowntime[Ended On] >= endate, DATEDIFF(sdate,endate,MINUTE),0) 

 )

 )

 )))

** CalendarDIM is my date table
** PlantDowntime = is the table that has StartedOn date and EndedOn Date. 

Thank you for your help 

[Eesa]

I created Start of Day in Power Query and join it with Date in my CalendarDIM table and have Duration column. If understand how PBI works, it will filter my Downtime table for that date and calculates the the duration regardless of the end date.

Table below shows on 4/4/2021 the duration 5214 minutes while i am expecting 1440 minutes on that date!

[amitchandak]

@Eesa , Hope you have the original DateTime column and you are taking the difference between them?

[Eesa]

@amitchandak  I used a calculated column to calculate the difference between the two dates. Duration(min) showing above is the calculated column it is not the measure. I hope this is clear

[amitchandak]

@Eesa , its is possible to share data

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Eesa]

Hi @amitchandak 

To view my folder, click this link:
https://1drv.ms/f/s!Aue3R0CwLM71pzMqJf2nvogdaZqt

Thank you so much for your help

[amitchandak]

@Eesa , Based on what I got, You can create a date from start date in your table and join that with date of calendar

Date = [Start Date].date
or
Date = date(year([Start Date]),month([Start Date]),day([Start Date]))

Power Query

DateTime.Date([Start Date])

You can have duration column

datediff([Start Date], [End Date], minute) // Change to second or hour as per need