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 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 ?

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

     

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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-trend/ba-p/882970

     

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

      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.

  • 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:)