Forum Discussion

Maya2988's avatar
Maya2988
Icon for Helper I rankHelper I
2 years ago
Solved

Need help in creating Date Difference in Power BI

Hi All,

 

I amtrying to create a power bi reporte using SQL Server Direct Query mode. I have the below columns with some sample values

 

 

NumberCompanyStatusDate
1XXXGD29/09/2014 15:58:27
1XXXUC29/09/2014 12:43:39
2YYYGD30/09/2014 15:58:27
2YYYUD30/09/2014 12:43:39
3ZZZGD27/09/2014 15:58:27
3ZZZUC27/09/2014 12:43:39

 

Need to create table or matrix to show the below output. So need help in achiving the below output in Power BI

 

NumberCompanyStatus Hours Difference Between
  GDUC 
1XXX29/09/2014 15:58:2729/09/2014 12:43:393:15
2YYY30/09/2014 15:58:2730/09/2014 12:43:393:15
3ZZZ27/09/2014 15:58:2727/09/2014 12:43:393:15

 

Any help in achiving this Hours Difference Between calculation in Power or SQL.

 

I tried in SQL and got the below output

 

NumberCompanyGD DateUC DateHours Difference Between
1XXX29/09/2014 15:58:27NULL 
1XXXNULL29/09/2014 12:43:39 
2YYY30/09/2014 15:58:27NULL 
2YYYNULL30/09/2014 12:43:39 
3ZZZ27/09/2014 15:58:27NULL 
3ZZZNULL27/09/2014 12:43:39 

 

Thanks,

Maya

Need Help
  • wini_R's avatar
    wini_R
    2 years ago

    Hi Maya2988,

     

    One way to achieve this is to create a new table based on the original one to get the expected structure and also set the right format for duration column:

     

     

  • wini_R's avatar
    wini_R
    2 years ago

    Hey Maya2988,

     

    Just include that column in SUMMARIZE function:

    table1 = 
ADDCOLUMNS(
    SUMMARIZE(
        tabA,
        tabA[Company],
        tabA[Number]
    )
    , "GD time", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "GD")
    , "UC time", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "UC")
    , "duration", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "GD") - CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "UC")
)

5 Replies

  • wini_R's avatar
    wini_R
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    Hi Maya2988,

     

    One way to achieve this is to create a new table based on the original one to get the expected structure and also set the right format for duration column:

     

     

  • Maya2988's avatar
    Maya2988
    Icon for Helper I rankHelper I
    2 years ago

    Hi wini_R 

     

    Thanks for the help. This helps me a lot and learnt a lot.Please tell me how to add Number also to this new table dax query (As i am new to power bi dont know how to do this)

    • wini_R's avatar
      wini_R
      Icon for Solution Supplier rankSolution Supplier
      2 years ago

      Hey Maya2988,

       

      Just include that column in SUMMARIZE function:

      table1 = 
ADDCOLUMNS(
    SUMMARIZE(
        tabA,
        tabA[Company],
        tabA[Number]
    )
    , "GD time", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "GD")
    , "UC time", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "UC")
    , "duration", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "GD") - CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "UC")
)
      • Maya2988's avatar
        Maya2988
        Icon for Helper I rankHelper I
        2 years ago

        Thanks wini_R . It works perfectly. Last question regarding performance. Creating new table using DAX in power bi will create any performace issue in future.