new column based on specific criteria on other columns

[amirghaderi]

Hi,

I need some help with a Dax code.

In table B, I want to add two new columns to be populated as the below criteria. (Time sheet Status/Overtime Status)

In table A: We have the below Info:

Organisation	Overtime Preferred
A	No
B	Yes
C	Yes

Organisation	Expenditure Type	Employee or Supplier	Quantity	Week Ending Date	Time Sheet Status(New)	Overtime Status (New)
A	Overtime Paid	John	10	20/11/2020	To be adjusted	To be adjusted
A	Regular Work	John	5	20/11/2020	To be adjusted	-
B	Regular Work	John	5	20/11/2020	To be adjusted	-
B	Regular Work	John	15	20/11/2020	To be adjusted	-
C	Regular Work	John	15	20/11/2020	To be adjusted	-
A	Regular Work	John	10	27/11/2020	Approved	Approved
B	Overtime Paid	John	5	27/11/2020	Approved	-
B	Regular Work	John	5	27/11/2020	Approved	-
B	Regular Work	John	10	27/11/2020	Approved	-
C	Regular Work	John	15	27/11/2020	Approved	-
A	Overtime Paid	John	5	4/12/2020	Approved	Approved
A	Regular Work	John	40	4/12/2020	Approved	-
B	Overtime Paid	John	5	11/12/2020	Approved	Approved
A	Regular Work	John	40	11/12/2020	Approved	-
B	Overtime Paid	John	0	18/12/2020	Approved	Approved
A	Regular Work	John	40	18/12/2020	Approved	-
A	Regular Work	John	10	25/12/2020	To be adjusted	-
A	Overtime Paid	John	5	25/12/2020	To be adjusted	To be adjusted
B	Overtime Paid	John	5	25/12/2020	To be adjusted	Approved
B	Regular Work	John	5	25/12/2020	To be adjusted	-
B	Regular Work	John	10	25/12/2020	To be adjusted	-
C	Regular Work	John	15	25/12/2020	To be adjusted	-

On each employee (John) and on each weekending,

If sum of quantity for Overtime Paid = 0 (example, weekending 18/12/2020)

then time sheet status “Approved, Overtime status “-“;

(If sum of quantity for Overtime Paid > 0 then,

1. If Overtime preferred for Organization is “Yes”’ in table A then overtime status “Approved” (27/11/2020,11/12/2020)
2. If Overtime preferred for Organization is “No”’ in table A then
   1. If only one organization has quantity >0, then  overtime status “Approved (4/12/2020)
   2. If more than one organization has quantity >0, then
      1.       If sum of regular work for Preferred overtime organization >0, then Overtime status, “to be adjusted, else ‘Approved” (20/11/2020,25/12/2020)

If All items in Overtime status is approved or “-“, then time sheet status to be “Approved”, else “To be adjusted”

In summary, we want to make checkemployees are timewriting in the first place against the organizations which “Overtime Preferred” is yes.

[AllisonKennedy · Accepted solution]

@amirghaderi  Fun little puzzle. I have created two new columns in your table: 

Timesheet Status =

VAR _WeekTotalQuantity = SUMX(FILTER(Timesheet, Timesheet[Employee or Supplier] = EARLIER(Timesheet[Employee or Supplier]) && Timesheet[Week Ending Date] = EARLIER(Timesheet[Week Ending Date]) ), Timesheet[Quantity])

VAR _OrganisationWeekQuantity = SUMX(FILTER(Timesheet, Timesheet[Employee or Supplier] = EARLIER(Timesheet[Employee or Supplier]) && Timesheet[Week Ending Date] = EARLIER(Timesheet[Week Ending Date]) && Timesheet[Organisation]=EARLIER(Timesheet[Organisation]) ), Timesheet[Quantity])

VAR _OrganisationOvertimeNotPreferred = SUMX(FILTER(Timesheet, Timesheet[Employee or Supplier] = EARLIER(Timesheet[Employee or Supplier]) && Timesheet[Week Ending Date] = EARLIER(Timesheet[Week Ending Date]) && RELATED(DimOrg[Overtime Preferred])="No" && Timesheet[Expenditure Type] = "Overtime Paid" ), Timesheet[Quantity])

RETURN

IF(_OrganisationOvertimeNotPreferred = 0, "Approved", IF(_OrganisationWeekQuantity = _WeekTotalQuantity, "Approved", "To be adjusted"))

Overtime Status =

IF(Timesheet[Expenditure Type] <> "Overtime Paid", "-", IF(RELATED(DimOrg[Overtime Preferred]) = "Yes", "Approved", Timesheet[Timesheet Status]))

Also please see attached sample file. 

[amirghaderi]

Thank you very much! It worked😊