Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Eesa
Frequent Visitor

Line Chart of Unrelated Tables

Hi all, 

 

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

Eesa_1-1640670375627.png

 

 

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

Eesa_0-1640669571000.jpeg

 

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 

6 REPLIES 6
Eesa
Frequent Visitor

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!

Eesa_0-1640672693654.png

 

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

@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

@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.

Hi @amitchandak 

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

Thank you so much for your help

amitchandak
Super User
Super User

@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

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors