cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Floating date of interest - administration delay

Hello everyone,

first thing first, sorry for my English.

I have an SQL table (direct query) with manufacturing batches, states (pre-production administrations, production, packing, etc.), time stamps (since, to) , planed terms (how long the batch can be in the state - for example every batch has 3 workdays to done packing) and others. SQL table autoaticly refreshes once a day.

I need to visualise all batches in delay, in timeline (day by day). I did this before when delays were exported every day, but you can imagine the size of data, which were multiplying every day.

 

This is how the visualisation should look like:

 

Admin_delays.JPG

In report, there must be possibility to switch between SUM of days in delay and SUM of batches in delay.

 

Columns in SQL table:

  • batch
  • state
  • state since (date)
  • state to (date)
  • plan (for example 3 days to switch state)
  • days of delay (final delay)

 

In the SQL table, there are batches with states without delays as well.

 

Could you please help me, how to make this happen?

a) day of interest (day on timeline - X axis)

b) delays of each batch in this day

 

Thank you very much.

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Floating date of interest - administration delay

Hi @Fishtron,

 

Here I made a sample as below. You can refer to the following steps.

 

1. Enter the sample data and create the calculated columns as below.

 

 

state = IF(DATEDIFF(Table1[state since],Table1[state to],DAY)>Table1[plan],"delay","on time")
delay days = IF(Table1[state]="delay",DATEDIFF(Table1[state since],Table1[state to],DAY)-Table1[plan],BLANK())

 

2. Create the measure as below.

 

 

count = CALCULATE(COUNT(Table1[batch]),FILTER(Table1,Table1[state]="delay"))
sumdays = CALCULATE(SUM(Table1[delay days]))

 

Then we can get the result as below.

 

Capture.PNG

 

For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly share your pbix to me.

 

https://www.dropbox.com/s/fqedpla3mwffdkp/Floating%20date.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Regular Visitor

Re: Floating date of interest - administration delay

Thank you very much for your respond Frank,

unfortunately I didn't explained my problem clearly.

 

I need to see amount of delays (count of days and count of delayed batches) in two separeted graphs day by day (days are X axis).

I hope images below will explain that.Workflow & report explanation.JPG

 

This is how my SQL direct query looks like:

Data_table_query.JPG

 

 

There are about 30 states but not all of them are interesting for this report so I will filter them. 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors