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

     

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?