Combining data from two tables.

[carlbh]

I have a challenge I am trying to overcome and I am hoping someone here has had a similar issue and can assist. 

I have a table containing all the cases managed by a helpdesk, date logged, duration, etc. 

There are multiple cases for any given day, likewise there are days with no cases. 

What I want to do is add the total duration of all cases handled in a given day in a seperate table that is a list of all days in year.

If there were no cases logged then I want the duration column in table 2 to show 0. 

If there were cases then I want the duration column in table 2 to show total of duration of all cases logged that date from table 1. 

I hope this makes sense. I am sure there is a way to do this, I just cant get my head round it. Fictional data to explain concept below. 

Table 1 Case Data
Logged Date Time	Case ID	Duration (Mins)
01/09/2021 09:00	100	10
01/09/2021 11:15	101	20
01/09/2021 13:30	102	30
03/09/2021 08:45	103	10
03/09/2020 09:30	104	15
04/09/2021 10:15	105	20
06/09/2021 09:30	106	50
06/09/2021 11:00	107	5
08/09/2021 13:15	108	20
08/09/2021 14:00	109	20
10/09/2021 17:00	110	10

Table 2 Total by Date
Date	Total Duration (mins)
01/09/2021	60		Sum of duration all cases logged on 1/9/21 e.g. Sum D4:D6
02/09/2021	0		Sum of duration all cases logged on 2/9/21 e.g.No cases on this day in case data table so returns 0
03/09/2021	25		Sum of duration all cases logged on 3/9/21 e.g.Sum of D7:D8
04/09/2021	20		Sum of duration all cases logged on 4/9/21 e.g.Sum of D9
05/09/2021	0		Sum of duration all cases logged on 5/9/21 e.g.No cases on this day in case data table so returns 0
06/09/2021	55		Sum of duration all cases logged on 6/9/21 e.g. Sum of D10:D11
07/09/2021	0		Sum of duration all cases logged on 7/9/21 e.g.No cases on this day in case data table so returns 0
08/09/2021	40		Sum of duration all cases logged on 8/9/21 e.g.Sum of D12:D13
09/09/2021	0		Sum of duration all cases logged on 9/9/21 e.g.No cases on this day in case data table so returns 0
10/09/2021	10		Sum of duration all cases logged on 10/9/21 e.g.Sum of D14

[PaulDBrown · Accepted solution]

Here is one way of achieving what you are after.

First of all, you need to split the Logged Date Time into date and time columns. In Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBBDoAgDAS/Yjib0KWgwFcI//+GKCWt0cteJjtbaM0RPBUfKGCjUonc7iDp+v7iQEV6CEaGL+fKsxlGsnBWf65x9tn42e5LP96ZJo/qp7WfzP7x0z9Gpg8f98vLzptPnO35os9Gb3hc9aIcpPwUjvV9/QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Logged Date Time" = _t, #"Case ID" = _t, #"Duration (Mins)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Logged Date Time", type datetime}, {"Case ID", Int64.Type}, {"Duration (Mins)", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Logged Date Time", type text}}, "es-ES"), "Logged Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Logged Date Time.1", "Logged Date Time.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Logged Date Time.1", type date}, {"Logged Date Time.2", type time}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Logged Date Time.1", "Logged Date"}, {"Logged Date Time.2", "Logged Time"}})
in
    #"Renamed Columns"

Which gets you this:

Nest create a Date Table using:

Date Table = 
VAR MinD = MIN('Table'[Logged Date])
VAR MaxD = MAX('Table'[Logged Date])
RETURN
CALENDAR(MinD, MaxD)

Create a relationship between the Date Table and the Logged Date field. The model looks like this:

Create a Measure for the duration:

Sum Duration = SUM('Table'[Duration (Mins)]) + 0

And finally create the visual using the date field from the Date Table and the [Sum Duration] measure to get:

I've attached the sample PBIX file

[Anonymous]

Create calculated table of dates using CALENDAR or CALENDARAUTO, then add calculated column with sum of durations, something like this total_duration=CALCULATE(SUMX('Case Data','Case Data'[Duration (Mins)])).