Need Help displaying duration based on 2 values

[RonaldvdH]

Guys, i have a question concerning the way to display the duration of permits.

I have, for example, permit A which is active between 01-01-2020 till 06-06-2020

I have 1 colum with values Permit A, Permit B etc

Then i have 1 colum with a date when the permit is given

And a column with a date when it expires

So basically

Type                                permit given                permit expires

Permit A                          01-01-2020                  06-06-2020

Permit B                          15-01-2020                  01-07-2020 

I want those values to be shown in a visual with a bar between 01-01-20 and 06-06-20 so i think i need a data table and then i need to mark those permits in that table or something but i dont know how, can you guys help me ?

[v-alq-msft]

Hi, @RonaldvdH 

Based on my research, I failed to solve it with a bar chart. Because when you put the date column in the 'Value' area of the visual, Power BI desktop operates an aggregation on it by default. As a workaround, you may use a table visual to show the duration.

You may create a data table as follows.

DateTable = CALENDARAUTO()

Then you can create a measure as below.

IsDisplay = 
var _type = SELECTEDVALUE('Table'[Type])
var _date = MAX(DateTable[Date])

return
IF(
    HASONEVALUE('Table'[Type]),
    IF(
        _date<=VALUES('Table'[permit expires])&&
        _date>=VALUES('Table'[permit given]),
        1,
        0
    ),0
)

Finally you may use the data column from data table to create a table visual and put the measure on the visual level filter. You can use the 'Type' column as a slicer to filter the result.

Best Regards

Allan

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

[RonaldvdH]

@v-alq-msft thanks for the help, ive made some progress but we are not there yet

The formula youve provided calculates and shows the dates between the dates permit given and permit expires

but somehow my result based on the actual data doesn't show the same result as your example data

IsDisplay =

var _type = SELECTEDVALUE(Vergunningen[Soort vergunning])

var _date = MAX('Date'[Date])

return

IF( HASONEVALUE(Vergunningen[Soort vergunning]);

IF( _date<=VALUES(Vergunningen[Vergunning geldig tot:])&&

_date>=VALUES(Vergunningen[Datum verleend]);

1; 0 );0)

What i dont understand is that when i filter a certain area and a specific permit, in this case the permits for the N280 only show the dates in the column Datum verleend (=Permit given) and not the entire range of dates between Permit given (=Datums verleend) and Permit expires (=Vergunning geldig tot:)

[RonaldvdH]

@v-alq-msft any ideas ? maybe its just a small thing we need to fix

[v-alq-msft]

Hi, @RonaldvdH 

The previous measure works only when you select one value and both Datum verleend and Vergunning geldig tot are not null. I wonder what the duration will be if there is one item selected and Vergunning geldig tot is null. I alse want to know the duration  when you have two items selected.

Best Regards

Allan

[RonaldvdH · Accepted solution]

@v-alq-msft and @amitchandak 

i want to thank you both for your help so far.

When i searched the internet for possible solutions i came across this custom visual

Gantt Chart By MAQ Software

And that is exactly what i need to fix the problem because i have a start date and sometimes an enddate and this visual displays it beautifully see the picture below

[amitchandak]

Refer my blog how deal with two dates (start and end)

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[RonaldvdH]

@amitchandak that kinda helped but didn't solve my problem (yet)

The idea is pretty much the same but still some formulas don't work or don't provide the desired outcome

In your example the formula's are based on a value in the first or the second column (start date or end date)

My question is more displaying the duration of a certain permit and seeing when a permit is about to expire given i only have a start and enddate.

[amitchandak]

datediff can you duration and if you use end date relation then you can tell permits expiring in a month.

Can you share better sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[RonaldvdH]

@amitchandak 

Here you can see some sample data

The type of permit is in column 'Soort vergunning' and 'Datum aangevraagd' is the date on which the permit has been requested and the column 'Datum verleend' is the column on which date the permit has been approved.

The column 'Vergunning geldig tot' is the date on which the permit expires 

this visual isn't correct (i know) but what i want is that the Y-axis has the types of permits and the X-axis has the dates/weeks (start/end/duration) so that i can visualise the duration of the permits