Build a New table based on other tables

[andrezmor]

Hi there. After some days I decided to come and ask for help. basically I need to build a new table based on two other tables. I am looking for DAX code to generate the desiered table.

I have a Date table

Date

1-Jun-22 00:00

2-Jun-22 00:00

3-Jun-22 00:00

4-Jun-22 00:00

5-Jun-22 00:00

6-Jun-22 00:00

7-Jun-22 00:00

8-Jun-22 00:00

9-Jun-22 00:00

10-Jun-22 00:00

11-Jun-22 00:00

12-Jun-22 00:00

13-Jun-22 00:00

I also have a Trips Table, where total hours is just the difference between start and end time

Name	Start	End	Total Hours
AES	2-Jun-22 20:16	3-Jun-22 13:36	17:20
CEN	3-Jun-22 14:36	6-Jun-22 02:57	60:21
ATL	3-Jun-22 16:21	6-Jun-22 01:55	57:34
LUM	6-Jun-22 03:57	6-Jun-22 21:44	17:47
MER	6-Jun-22 02:55	8-Jun-22 11:19	56:24

I need to generate the following table, where I summarize the number of hours per day per Name:

Date	Name	Hours
1-Jun-22 00:00	---	00:00
2-Jun-22 00:00	AES	03:44
3-Jun-22 00:00	AES	13:36
3-Jun-22 00:00	CEN	09:24
4-Jun-22 00:00	CEN	24:00
5-Jun-22 00:00	CEN	24:00
6-Jun-22 00:00	CEN	02:57
3-Jun-22 00:00	ATL	07:39
4-Jun-22 00:00	ATL	24:00
5-Jun-22 00:00	ATL	24:00
6-Jun-22 00:00	ATL	01:55
6-Jun-22 00:00	LUM	17:47
6-Jun-22 00:00	MER	21:05
7-Jun-22 00:00	MER	24:00
8-Jun-22 00:00	MER	11:19
9-Jun-22 00:00	---	00:00
10-Jun-22 00:00	---	00:00
11-Jun-22 00:00	---	00:00
12-Jun-22 00:00	---	00:00
13-Jun-22 00:00	---	00:00

I appreciate your help.

[Anonymous · Accepted solution]

HI @andrezmor,

You can use the following calculated column formula to get the expand result based on crossjoin function and filters:

Expand = 
SELECTCOLUMNS (
    ADDCOLUMNS (
        FILTER (
            CROSSJOIN ( 'Table', 'Calendar' ),
            [Date] >= DATEVALUE ( [Start] )
                && [Date] <= DATEVALUE ( [End] )
        ),
        "Hour",
            VAR diff =
                IF (
                    DATEVALUE ( [Start] ) >= [Date],
                    DATEDIFF ( MAX ( [Start], [Date] ), DATEVALUE ( [Start] ) + 1, MINUTE ),
                    DATEDIFF ( DATEVALUE ( [Date] ), MIN ( [End], [Date] + 1 ), MINUTE )
                )
            RETURN
                INT ( diff / 60 ) & ":"
                    & MOD ( diff, 60 )
    ),
    "Name", [Name],
    "Date", [Date],
    "Hour", [Hour]
)

Regards,

Xiaoxin Sheng

[lbendlin]

Looks like there is a bug in the DAX engine that does not handle certain time values well.

This measure is how it is supposed to work

Total hours = 
var n = summarize(Results,Results[Name],Results[Start],Results[End],Results[Date])
var b = ADDCOLUMNS(n,"m",countrows(intersect(GENERATESERIES([Date]*1440,([Date]+1)*1440-1),GENERATESERIES([Start]*1440,[End]*1440-1))))
return format(0+int(sumx(b,[m])/60),"00:")&format(0+mod(sumx(b,[m]),60),"00")

But it comes up empty for two events for some bizarre reason.

This calculated column works ok, in contrast.

Minutes = 
switch (TRUE()
,int([Start])=int([Date]) && int([End])=int([Date]),([End]-[Start])*1440  -- start and end on same day
,[Date]>=[Start] && int([Date])<int([End]),1440 -- full day
,int([Date])=int([Start]),1440-([Start]-int([Start]))*1440 -- first day
,int([Date])=int([End]),([End]-int([End]))*1440  ) -- last day

See attached for the result.

NOTE:  Your desired result is unachievable. You need to make compromises by using a matrix, for example.

see attached