Ideas on how to visualise this needed

[Anonymous]

I'm not sure if this is the place to post this, if not, I will move/remove it if advised...

I'm looking for some visualisation ideas. I'm an experienced Power BI Developer, but totally lost for ideas on how to achieve this brief:

To give context:

I have around 20,000 open, in progress, orders at any point in time, the dataset has the following on each row:

Order number

Date order created

Reporting date

Planned ship date

other columns with information about the order

Each order is added as a new row to the data for every date it is open (reporting date) and then disappears from the data entirely the day after it is completed, the planned ship date is usually a future date, but not always as the shipment may have been missed and not yet rescheduled.

What I want to achieve:

I am trying to find a way to visualise this so that I can track counts of new orders, how many have been completed, how many were active at each point in time and how that is trending over time. I'd also like to see, for a given date,how many were due to be shipped in the future and how many missed (this one is easy, but perhaps relevant to any ideas)

In my mind I'm trying to find a way to create a continuous waterfall that shows the ups and downs every day, or rolled up to every week, month, etc, does that exist or are there any ideas on other ways to visualise this?

I've tried so many ways of cutting the data and different chart option and nothing really gives me the view that i'm after - I'm think I'm now blinded by trying so many things I feel I'm going in circles!

Help, please!

[v-shex-msft]

HI @Anonymous,

It seems like a common requirement about visualization and aggregate records across multiple date fields, I'd like to suggest you create a calculated table to expand date range records for analysis.

You can check the below steps if they meet for your request.

1. Create a expand table based on 'order number' and its date ranges.

Expand = 
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN (
            'Table',
            CALENDAR ( MIN ( 'Table'[Planned ship date] ), MAX ( 'Table'[Reporting date] ) )
        ),
        [Date] >= [Planned ship date]
            && [Date] <= [Reporting date]
    ),
    "Order number", [Order number],
    "Date", [Date]
)

2. Build a relationship from 'expand' table to the original table based on 'order number' field with 'both' direction mode.

3. Create a line chart with 'expand' table date as axis, original table 'order desc' field to legend, 'order number' as value. 

Regards,

Xiaxoin Sheng

[Anonymous]

Hi @v-shex-msft ,

Thanks for coming back to me.

This doesn't actually meet my requirements, but I consider myself pretty experienced at Power BI and DAX, and I cannot fathom how your "expand" DAX works. Can you explain it?

In order to explain my requirements: I have a table with a row for every order, for every day it is open - so if an order is opened on 1st March, with a planned date of 15th March, but fulfilled on 10th March, I would see a row for this order number for every day between 1st March and 10th March in my dataset (all showing the planned date at whatever it was on the reporting date - the planned date is likely to move). Now multiply that by 20,000 orders open at any point in time. I'm not really too worried about the planned date at the moment, that's a future development.

What I am trying to do is find a good way to visualise this movement, to show those being created, and fulfilled in a continuous view, so that i can track performance.

[ranbeermakin · Accepted solution]

Hi @Anonymous 

I'm afraid if one visual can suffice your needs. However, here are few tips and visuals that can help you analyze :

1. Use a funnel chart (horizontal funnel) to show overall progression. New orders -> completed -> active

2. Use column charts to show total scheduled vs missed, you can aggregate on weeks or months

3. Use a heatmap chart to show order movement. X axis has dates and Y axis has orders. Example of this attached. This is done in R.

[Anonymous]

@ranbeermakin Thanks for this. I will see if I can get this working. I like the idea.

[ranbeermakin]

Thanks

-Ranbeer

If this post helps, please consider Accept it as the solution to help the other members find it more quickly

[v-shex-msft]

Hi @Anonymous,

For power bi visual, it not able to analyze records across multiple date ranges, it can only analytics with one of them as the axis. Your original table rows have folded multiple records of a date range to one and remark with two date fields.

My calculated table 'expand' is used to expand these folded records and mapping with the original id field. (original table date range has 10 days, you can find out the detail records from the start date to end date in 'expand table' instead of one row)
Since folded detail records link to the original table, you can use aggregate methods on visuals value fields to summarize and display the correct results.

Reference:

Spread revenue across period based on start and end date, slice and dase this using different dates 

BTW, if you still confused about these, please share some dummy data for test.

Regards,

Xiaoxin Sheng