Need help in creating Date Difference in Power BI

[Maya2988]

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

Number	Company	Status	Date
1	XXX	GD	29/09/2014 15:58:27
1	XXX	UC	29/09/2014 12:43:39
2	YYY	GD	30/09/2014 15:58:27
2	YYY	UD	30/09/2014 12:43:39
3	ZZZ	GD	27/09/2014 15:58:27
3	ZZZ	UC	27/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

Number	Company	Status		Hours Difference Between
		GD	UC
1	XXX	29/09/2014 15:58:27	29/09/2014 12:43:39	3:15
2	YYY	30/09/2014 15:58:27	30/09/2014 12:43:39	3:15
3	ZZZ	27/09/2014 15:58:27	27/09/2014 12:43:39	3:15

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

I tried in SQL and got the below output

Number	Company	GD Date	UC Date	Hours Difference Between
1	XXX	29/09/2014 15:58:27	NULL
1	XXX	NULL	29/09/2014 12:43:39
2	YYY	30/09/2014 15:58:27	NULL
2	YYY	NULL	30/09/2014 12:43:39
3	ZZZ	27/09/2014 15:58:27	NULL
3	ZZZ	NULL	27/09/2014 12:43:39

Thanks,

Maya

[wini_R · Accepted solution]

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]

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 · Accepted solution]

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]

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. 

[wini_R]

Yes, that might impact the performance depending on how big the original table is. It is always recommended (whenever possible) to do any tranformations in source system or as close to the source as possible (i.e. in SQL db).

[wini_R · Accepted solution]

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: