cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pedrohp503
Helper II
Helper II

Summarize date column with more than one criteria

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.

 

WhatsApp Image 2022-05-14 at 11.13.46 PM.jpeg

 

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.

2 ACCEPTED SOLUTIONS
SpartaBI
Community Champion
Community Champion

@pedrohp503 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])) 

 

SpartaBI_0-1652636134007.png

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:

SpartaBI_1-1652636293753.png


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]))
			)
	)

 

 

SpartaBI_2-1652636349974.png


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]))
)

 

SpartaBI_3-1652636442088.png

 


5. Format that elapsed time column like that:

SpartaBI_4-1652636525203.png

 

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

SpartaBI
Community Champion
Community Champion

@pedrohp503 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.


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

7 REPLIES 7
SpartaBI
Community Champion
Community Champion

@pedrohp503 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)

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
Community Champion
Community Champion

@pedrohp503 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])) 

 

SpartaBI_0-1652636134007.png

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:

SpartaBI_1-1652636293753.png


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]))
			)
	)

 

 

SpartaBI_2-1652636349974.png


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]))
)

 

SpartaBI_3-1652636442088.png

 


5. Format that elapsed time column like that:

SpartaBI_4-1652636525203.png

 

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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
Community Champion
Community Champion

@pedrohp503 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.


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

It also worked just fine, @SpartaBI.

 

Thank you very much for the thorough explanation.

SpartaBI
Community Champion
Community Champion

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors