Forum Discussion

HelenaX's avatar
HelenaX
Icon for Helper I rankHelper I
5 years ago
Solved

Creating a chart with finish time as the value

I have a table which shows the finish time of a program (called CCP in the screen shot) for multiple countries. I would like to create a bar chart to show the different finishing time of each country. It seems to be an easy thing to do. However, when I created the bar chart, the finish time is automactially counted instead of just listed as the actual value. 

 

 

If I change the value to the "Duration" of the program, the bar chart worked as expected: 

Ideally, I would like the bar chart to show the program finish time for various countries. So the X-Axis will show the time dimension such as 12:00, 12:30, 01:00 etc and the bar will show the finish time falling within these dimensions. So it will look like the Duration chart above but with time dimension as the X-Axis. For instance, for a country with finish time of 12:21, it will have a bar starting from 0 and go all the way to somewhere between the time dimension of 12:00 and 12:30. 

 

Currently, the finish time has data type of "Time" with format of h:nn:

 

I also linked a Time table which has all the needed time dimension around the clock. 

 

Could someone help me out please?

 

Thanks a lot!

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi HelenaX ,

     

    You also can use the following m query to calculate duration:

     

    Table.AddColumn(Source,"dunration", each Duration.Minutes([end time]-[start time]))

     

    Or use the following measure:

     

    CCP Duration = DATEDIFF(MAX(CCP_STATUS[CCP Start Time]), MAX(CCP_STATUS[CCP Finish Time]), minute)

     

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

     

    Best Regards,

    Dedmon Dai

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    HelenaX , Power Bi standard visual does not show time on the y axis.

    You can have time in a minute or second

     

    Time in Min = Time(hour([datetime]) *60 + minute([datetime]) + second([datetime])/60

    • HelenaX's avatar
      HelenaX
      Icon for Helper I rankHelper I
      5 years ago

      Thanks amitchandak for providing the information. I will then stop pursuing my original idea. Now since the duration works, I am thinking to generate a different chart using the duration time. I am thinking to generate bars  to show the average duration of the program in each country (over all the data available). In addition, I would also like to generate a bar for current day's duration and then compare it with the average duration. Basicaly, the visual will look like the chart for the duration in my original question. But the bars will show average duration with light color in the background and then current day's average to appear on the same bar but in the front. For instance, the average duration for a country is 50, this will be showing as a bar with light color in the background. Today's duration is 40 min, this bar overlaps with the average bar as green color, you can then easily tell that it finished 10 min earlier comparing to the average duration time. On the other hand, if today's duration is 65 min, the extra 15 min will show as 15 min extended red bar from the end of the average bar so you can tell easily that the the duration is longer than the average for 15 min. Do you think this is possible? If so, what measure/columns do I need to create? And which chart is available?

  • HelenaX's avatar
    HelenaX
    Icon for Helper I rankHelper I
    5 years ago

    Could someone please tell me how to create a measure to calculate a time duration? I was able to calculate the "CCP Duration" by creating a new column and use below formula:

     

    CCP Duration = DATEDIFF(CCP_STATUS[CCP Start Time], CCP_STATUS[CCP Finish Time], minute)
     
    which is basically subtracting the "CCP Start Time" column from the "CCP Finish Time" column:
     

     

    However, when I tried to create a measure by using the Quick Measure, it doesn't use the actual value of the Finish or Start Time but used Count
     

    So, how do I create a measure to simply replicating the formula of 

    CCP Duration = DATEDIFF(CCP_STATUS[CCP Start Time], CCP_STATUS[CCP Finish Time], minute)

     

    I don't know about DAX and found it difficult to do some pretty simple calculations in PowerBI. 

     

    I would really appreciate your help. 

    • v-deddai1-msft's avatar
      v-deddai1-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi HelenaX ,

       

      You also can use the following m query to calculate duration:

       

      Table.AddColumn(Source,"dunration", each Duration.Minutes([end time]-[start time]))

       

      Or use the following measure:

       

      CCP Duration = DATEDIFF(MAX(CCP_STATUS[CCP Start Time]), MAX(CCP_STATUS[CCP Finish Time]), minute)

       

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

       

      Best Regards,

      Dedmon Dai

       

      • HelenaX's avatar
        HelenaX
        Icon for Helper I rankHelper I
        5 years ago

        v-deddai1-msft Yes, this method works too! The MAX function intially gave me an impression that it is giong to calculate the maximum value of the whole Start Time column and then subtract it from the maximum value of the whole Finish Time column. It is to my pleasant surprise that it is calculating row by row. So the MAX here doesn't really mean it is calculating the maximum value of the whole column? Any reason why it is called MAX then?