topic Re: MAX Date date not working in variable in DAX Commands and Tips

MAX Date date not working in variable

EWBWEBB — Fri, 09 Dec 2022 16:34:51 GMT

I have 2 tables, one which contains all my people information the other table contains their sick days and reasons.

StaffAbsence contains the following

Absence Key, Date, PersonRef, Period

A People Table where the list repeats every period for the employees this is laid out as follows

AbsenceKey,PersRef,Period,StartDate,LeaveDate,FirstDateofPeriod,LastDayofPeriod

What I would like to do is count all sickness days from the staff absence table which are in the period. However there are some instances where the person has a logged days sickness after there leave date.

So I want to count all sickness days in the period unless there is a leave date then I want to count to the leave date or period end date whichever is sooner.

This is my dax but it doesn't seem to be registering the <= and appears that this is being overrules somewhere.

SickeDays = VAR StartDate = MINX(STAFFPERIODIC,STAFFPERIODIC[First Date of Period]) VAR LeaveDate = MAXX(STAFFPERIODIC,STAFFPERIODIC[LEAVE_DATE]) VAR PeriodEnd = MAXX(STAFFPERIODIC,STAFFPERIODIC[Last Day of Period]) VAR MaxDate = IF(LeaveDate < PeriodEnd, LeaveDate, PeriodEnd) VAR Result = CALCULATE( COUNTROWS(STAFFABSENCE), FILTER(STAFFABSENCE,STAFFABSENCE[DATE] >= StartDate && STAFFABSENCE[DATE] <= MaxDate || LeaveDate = BLANK() ) ) RETURN Result

Re: MAX Date date not working in variable

lbendlin — Sat, 10 Dec 2022 18:21:00 GMT

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: MAX Date date not working in variable

EWBWEBB — Mon, 12 Dec 2022 11:07:00 GMT

lbendlin - thank you.

These two tables would be joined on the absence key.
Absence

AbsenceKey	Date	PersonRef	Period
123452022-11	28/10/2022	12345	2022-11
123452022-11	29/10/2022	12345	2022-11
123452022-11	30/10/2022	12345	2022-11
123452022-11	31/10/2022	12345	2022-11
123452022-11	01/11/2022	12345	2022-11
123452022-11	02/11/2022	12345	2022-11
123452022-11	03/11/2022	12345	2022-11
123452022-11	04/11/2022	12345	2022-11
123452022-11	05/11/2022	12345	2022-11
123452022-11	06/11/2022	12345	2022-11
123452022-11	07/11/2022	12345	2022-11
123452022-11	08/11/2022	12345	2022-11
123452022-11	09/11/2022	12345	2022-11
123452022-11	10/11/2022	12345	2022-11
123452022-11	11/11/2022	12345	2022-11
123452022-11	12/11/2022	12345	2022-11
101112022-11	28/10/2022	10111	2022-11
101112022-11	29/10/2022	10111	2022-11
101112022-11	30/10/2022	10111	2022-11
101112022-11	31/10/2022	10111	2022-11
101112022-11	01/11/2022	10111	2022-11
101112022-11	02/11/2022	10111	2022-11
101112022-11	03/11/2022	10111	2022-11
101112022-11	04/11/2022	10111	2022-11
101112022-11	05/11/2022	10111	2022-11
101112022-11	06/11/2022	10111	2022-11
101112022-11	07/11/2022	10111	2022-11
101112022-11	08/11/2022	10111	2022-11
101112022-11	09/11/2022	10111	2022-11
101112022-11	10/11/2022	10111	2022-11
101112022-11	11/11/2022	10111	2022-11
101112022-11	12/11/2022	10111	2022-11
101112022-10	15/10/2022	10111	2022-10
101112022-10	14/10/2022	10111	2022-10
101112022-10	13/10/2022	10111	2022-10
101112022-10	12/10/2022	10111	2022-10
101112022-10	11/10/2022	10111	2022-10
123452022-10	15/10/2022	12345	2022-10
123452022-10	14/10/2022	12345	2022-10
123452022-10	13/10/2022	12345	2022-10
123452022-10	12/10/2022	12345	2022-10
123452022-10	11/10/2022	12345	2022-10

People

AbsenceKey	PersRef	Period	StartDate	LeaveDate	FirstDateofPeriod	LastDayofPeriod
123452022-11	12345	2022-11	01/01/2000	11/12/2022	16/10/2022	12/12/2022
101112022-11	10111	2022-11	01/01/2000		16/10/2022	12/12/2022
101112022-10	10111	2022-10	01/01/2000		18/09/2022	15/10/2022
123452022-10	12345	2022-10	01/01/2000	11/12/2022	18/09/2022	15/10/2022

The output I would expect would be:

In Period 2022-10
12345 - would count 5 days

10111 - would count 5 days

In Period 2022-11
12345 - Would count 15 days (with the 12/11/2022 not being counted as it is after the leave date)

10111 - Would be 16 days (Counting the 12/11/2022 as they do not have a leave date)