Summarize date column with more than one criteria

[Anonymous]

Hello,

Is there any way to summarize a table based on two information of the same column (in this case, “Log date/time”)? For instance, I update a spreadsheet on a daily basis and I have to calculate how much time some employees worked on the previous day. However, I must calculate this time by doing a subtraction of the first and last record of each person, but one person can (and actually) have more than one record of login and disconect in the very same day.

I can't post my DAX code because so far I've got nothing yet. I've tried to compute it by using SUMMARIZE, but every formula did not work properly. Can someone help me out to find a solution, please? I imagined a way to do so by at first summarize the fact table into a smaller one and thereafter doing the subtraction of the values (Disconect - Log in), but if one knows how to compute it without this middle step, i.e. just do the calculation without summarazing the fact table, I really appreciate the help. Thanks in advance.

P.S.: if it is easier to calculate/filter in DAX instead of Power Query or the other way around, no problem, because I can use any of these.

[SpartaBI · Accepted solution]

@Anonymous these are the steps:
1. Create this calculated column in 'Planilha1' table:

log Date = DATE(YEAR(Planilha1[Log date/time]), MONTH(Planilha1[Log date/time]), DAY(Planilha1[Log date/time])) 

2. Not related to the solution, but a very important note: You created a relationship between the date table and the datetime column in 'Planilha1'. This will not work as you think. It's not a group by rather an exact match. This is another reason to create the date column from point 1 to the model. Then you create the relationship as 1 to many with this column:

3. Moving back to the solution: Create this calculated table:

Summarized table = 
ADDCOLUMNS(
		SUMMARIZE(
			Planilha1,
			Planilha1[Employee],
			Planilha1[log Date],
			Planilha1[Status]
		),
		"Log date/time", 
			IF(
				Planilha1[Status] = "Log in",
				CALCULATE(MIN(Planilha1[Log date/time])),
				CALCULATE(MAX(Planilha1[Log date/time]))
			)
	)

4. Then create the final table (I could have done it in one step, but it's good to see for learning porpuses for you):

Ultimate Table = 
ADDCOLUMNS(
    SUMMARIZE(
        'Summarized table',
        'Summarized table'[Employee],
        'Summarized table'[log Date]
    ),
    "Elapsed time", 
        CALCULATE(MAX('Summarized table'[Log date/time]))
                - CALCULATE(MIN('Summarized table'[Log date/time]))
)

5. Format that elapsed time column like that:

Showcase Report – Contoso By SpartaBI

     

[SpartaBI]

@Anonymous can you share the sample table (not a screenshot) and I will write to you the code.
(You can copy paste a table to the message body)

[Anonymous]

Hey, @SpartaBI.

This link (or this one, in case the first don't work) contains the file sample file you suggested. That file is just a reduced example, because I cant share the original one due to confidential information of my work.

Thanks in advance.

[SpartaBI · Accepted solution]

@Anonymous these are the steps:
1. Create this calculated column in 'Planilha1' table:

log Date = DATE(YEAR(Planilha1[Log date/time]), MONTH(Planilha1[Log date/time]), DAY(Planilha1[Log date/time])) 

2. Not related to the solution, but a very important note: You created a relationship between the date table and the datetime column in 'Planilha1'. This will not work as you think. It's not a group by rather an exact match. This is another reason to create the date column from point 1 to the model. Then you create the relationship as 1 to many with this column:

3. Moving back to the solution: Create this calculated table:

Summarized table = 
ADDCOLUMNS(
		SUMMARIZE(
			Planilha1,
			Planilha1[Employee],
			Planilha1[log Date],
			Planilha1[Status]
		),
		"Log date/time", 
			IF(
				Planilha1[Status] = "Log in",
				CALCULATE(MIN(Planilha1[Log date/time])),
				CALCULATE(MAX(Planilha1[Log date/time]))
			)
	)

4. Then create the final table (I could have done it in one step, but it's good to see for learning porpuses for you):

Ultimate Table = 
ADDCOLUMNS(
    SUMMARIZE(
        'Summarized table',
        'Summarized table'[Employee],
        'Summarized table'[log Date]
    ),
    "Elapsed time", 
        CALCULATE(MAX('Summarized table'[Log date/time]))
                - CALCULATE(MIN('Summarized table'[Log date/time]))
)

5. Format that elapsed time column like that:

Showcase Report – Contoso By SpartaBI

     

[Anonymous]

Wow, I can say nothing but thank you, buddy. It was exactly what I aim to do and it worked perfectly.

Just a point: you said that for learning porpuses you break it through two steps, however you could've done it in a single one. Could you explain me how to do that in a single step? My model is a huge dataset and in order to shrink the file size I'd prefer to do it in one shot.

Once again, thanks in advance.

[SpartaBI · Accepted solution]

@Anonymous sure,

EVALUATE
ADDCOLUMNS(
    SUMMARIZE(
        'Planilha1',
        'Planilha1'[Employee],
        'Planilha1'[log Date]
    ),
    "Elapsed time", 
        CALCULATE(MAX('Planilha1'[Log date/time]), 'Planilha1'[Status] = "Disconect" )
                - CALCULATE(MIN('Planilha1'[Log date/time]), 'Planilha1'[Status] = "Log in" )
)

Pay attention that in this case peter has a different data in 01/02/2022. In the data you supplied he only disconnect that day but don't log in. In case this is somthing that can happen you need to decide how you want to deal with that. Each verison of the solution can be either way of course.
Also, pay attention regarding the word "disconect". I left the typo there so your formula won't break.

Showcase Report – Contoso By SpartaBI

     

[Anonymous]

It also worked just fine, @SpartaBI.

Thank you very much for the thorough explanation.

[SpartaBI]

@Anonymous my pleasure :). You can also accept the alternative solution message as a solution for community visabilty.