Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
6 years ago
Solved

Need Help displaying duration based on 2 values

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 ...
Need Help
  • RonaldvdH's avatar
    RonaldvdH
    6 years ago

    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

     

v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

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's avatar
    RonaldvdH
    Post Patron
    6 years ago

    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's avatar
      RonaldvdH
      Post Patron
      6 years ago

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

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        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