Add a column that calculates based on groups of rows from another column

[Anonymous]

Hi! I'm starting to learn Power BI and although is very intuitive I don't know how to create columns that use only some rows of another column to make calculations, "reading" along the rows. 

Here's the problem, I need to count the number of times a person worked in every "n" days (let's say n=4 and call this measure NT4) and also I need to count how many repeated times the person has been working nonstop (let's call this NRT).

For example, in this table we have workers A and B and we marked the dates when each of them worked.

NRT: we can see that B worked 10, 11 and 12 of May, so his NRT is 2 by 12-May, then he rests on 13-may, so that's why on 14-may he appears null again, than he starts working again on 14 May and the count starts again.

NT4: B worked 10, 11, 12 and then on 14 we count how many times he worked the last 4 days (that would be 11, 12 and 14) and he gets a 3 on NT4. Later we check on 15-may and he gets a 3 again (he worked 12, 14 and 15). By May 17th he gets a 4, because he worked on 14,15,16 and 17.

Sorry if my explanation is a bit confusing, is a little difficult to explain and understand. I hope you can help me on this, I really need to understand some delicate scenarios.

Best Regards,
Gabriela

[parry2k]

@Anonymous it is bit to hard to understand the challenge, can you share sample pbix file (remove sensitive info before sharing) and explain what your end result should look like. It will help to provide the appropirate solution.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi @parry2k thanks for the interest, it's been a long time since I saw this post. In fact, now that I read my description I don't understand a thing, so I'll tell you the real deal.

I have to analize a KPI called Alternating Shifts (AS) that calculates hoy many alternating shifts a person did (they can't work two shifts in a row, and there are 3 shifts every day). So let's say A worked today on shift 1 and 3 and tomorrow he'll work on shift two, we got this table:

		Friday			Saturday			Sunday
Worker	1	2	3	1	2	3	1	2	3
A	x		x		x			x
Alternating Shift = 2

This into a database looks like this:

	Date	Shift	Alternating Shift	Real Alternating Shift
A	Friday	1	0	0
A	Friday	3	1	0
A	Saturday	2	2	2
A	Sunday	2	0	0

With a formula and proper sorting (worker ID then Date then shift) I can calculate the AS and also I calculate the Real Alternating Shift (RAS) wich takes the real value of the AS (to avoid duplicating), on Excel.

This is what I need to calculate on Power BI, it could be with a calculated column or with a measure, I'd like both (because sometimes I need to see the real value and sometimes I'd like to evaluate the scenario in wich I can se the RAS if the person wouldn't have worked in certain days). A friend told me he guess this could be made with M instead of DAX, I don't know really how this can work and nobody has been able to help me; I hope you can,

Thank you for your time,
Best Regards,
Gabriela

P.S: I don't know how to add the Excel file 😞

[parry2k]

@Anonymous You can share excel file thru Onedrive/google drive link. I will be looking at this later today and hope that is ok.

Would appreciate Kudos 🙂 if my solution helped.

[parry2k]

@Anonymous I gone thru your post, I think I know what you are looking for, if you can share excel file thru onedrive/google drive with your formula, it will help to mimic the same in power bi. I'm interested to resolve it as this seems to be very interesting problem 🙂

[parry2k]

@Anonymous hey just checking if you had chance to attach the excel file with formula. I have time this weekend and will able to work on it. Cheers!!

[Anonymous]

Hi @parry2k sorry for the delay, I was totally disconnected. Thanks for the interest, there's a lot of complex scenarios and KPI's on this industry, starting with how to make a calendar with time intelligence that contemplates the three shifts.
Well, I hope you can help me, by the way, what is a Kudos?

Here's the Excel Sample 
Thank you for your time,
Best Regards,
Gabriela Sofía

[edhans]

I'm looking at this. I got your Excel function broken down where I can read it:

=IFERROR(
	IF(
		AND(
			OR(
				AND(
					INT(D1)=1,
					INT([@[Shift N°]])=3,
					[@Date]=C1
					),
				AND(
					INT(D1)=3,
					INT([@[Shift N°]])=2,
					[@Date]=C1+1
					),
				AND(
					INT([@[Shift N°]])=2,
					INT([@[Shift N°]])=1,
					[@Date]=C1+1
					)
				),
			B1=[@WorkerID]
			),
		E1+1,
		0
		)
	,0)

Now I need to map this out to how this looks in Power Query, which I've got into a table that will allow me to do that. I think. 😁 I'll have to get back to this later tonight or tomorrow when I can print this off and do some mapping of the fields and logic.