topic Duration of Days - Bar Chart in Power BI in Custom Visuals Development Discussion

Duration of Days - Bar Chart in Power BI

Hasna95 — Fri, 01 Mar 2024 09:12:41 GMT

Hi.
So I am facing this issue, and I have not seen it anywhere.
I basically have this table :

PR Number	Department	PR Status	Year	Vendor	Date	PR Raised by	PR Approved Date(LM)	PR Approved Date(Procurement)	SLA Met	Purchase Type	PR Order Creation Date
PR0001	Procurement	Open	2024	Vendor 1	19/06/2024	John	22/06/2024	22/07/2024	No	RFP	23/07/2024
PR0002	Operations	Open	2024	Vendor 2	31/01/2024	Alice	03/02/2024	28/02/2024	No	RFQ	29/02/2024
PR0003	Finance	Closed	2024	Vendor 3	01/02/2024	Bob	10/02/2024	10/03/2024	No	Exception	10/03/2024
PR0004	Marketing	Closed	2024	Vendor 4	07/02/2024	Carol	15/02/2024	16/02/2024	Yes	Exception	18/02/2024
PR0005	IT	Open	2024	Vendor 5	15/02/2024	David	18/02/2024	18/03/2024	No	RFP	19/03/2024

I have 3 durations, I would like to show:
1. Duration of days from Date to PR Approved Date (LM)
2. Duration of days from PR Approved Date (LM) to PR Approved Date (Procurement)
3. Duration of days from PR Approved Date (Procurement) to PR order Creation Date

I want to show these durations for each Purchase Type
Example: RFQ, RFP, and Exception.

So when I filter through Department or PR number, I can know what was all the 3 durations for each Purchase Type.

I want something like this:

I know I can recreate it bar chart or a matrix visual. But the issue I am facing is that, I have 3 durations for each Purchase Type.
That's why it's not coming the way I want it to.

Can you anyone suggest what I can do?

Re: Duration of Days - Bar Chart in Power BI

lbendlin — Fri, 01 Mar 2024 21:11:20 GMT

First step is to bring the data into a usable format

Project

Purchase Type

Interval type

From

Then use custom visuals like Deneb to show multiple date ranges in the same bar chart.

Re: Duration of Days - Bar Chart in Power BI

Hasna95 — Sat, 02 Mar 2024 17:30:45 GMT

I'm not too sure if I understand you.

Re: Duration of Days - Bar Chart in Power BI

lbendlin — Sat, 02 Mar 2024 18:39:18 GMT

Your data is not in a usable format. It is coming from Excel where wide tables with many columns are common. Power BI expects narrow tables with limited number of columns. Unpivot your data with the headers that I suggested, or your own choice of headers.

Then use Deneb to plot the data

Re: Duration of Days - Bar Chart in Power BI

Hasna95 — Tue, 05 Mar 2024 08:23:51 GMT

What I am looking for is basically, for each PR Number, I need to see the duration days from LM Approved, Procurement Approved and PO Creation Date (Clsoed)
I don't need the dates. I need the duration days rather similar to a gantt chart. But not sure how to structure my table.

Re: Duration of Days - Bar Chart in Power BI

lbendlin — Tue, 05 Mar 2024 20:40:25 GMT

You can get that by summing the differences between From and To.

Re: Duration of Days - Bar Chart in Power BI

Hasna95 — Wed, 06 Mar 2024 11:21:59 GMT

I did all that.
I have this formula

OverallStartDate Trial Measure =

MINX(

SUMMARIZE(Trial, Trial[PR Number], "MinStartDate", MIN(Trial[MinStartDate])),

[MinStartDate]

)
and this

Bar Shifting Days = SWITCH( TRUE(), Trial[Interval] = "LM Approved", Trial[Duration], Trial[Interval] = "Procurement Approved", DATEDIFF(Trial[From], Trial[To], DAY), Trial[Interval] = "Closed", DATEDIFF(Trial[From], Trial[To], DAY), BLANK() )

the issue is, if Closed is 1, instead of coming after the Procurement Approved It comes right under 0.

Closed should come after Procurement Approved.
I hope I am making sense.

Re: Duration of Days - Bar Chart in Power BI

Hasna95 — Wed, 06 Mar 2024 12:33:03 GMT

and for duration, I have this formula.

Duration = DATEDIFF('Table Demo'[From], 'Table Demo'[To], DAY)

After transforming the table, this is how it is structured.