cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joshua_dillon
Regular Visitor

Calculating Total Number of Employees Using Max Date and Min Date Variable/Measure

I'm hoping that I can explain what I am needing and it makes sense.  I have 2 tables: a Calendar table that is used as a DAX generated table used for a date slicer and then an Employee table.

 

Below is a very small sample size of Employee data to help support what it is I am trying to do.
TypeEffective DateManufacturerUnique ID
Vaccination05/19/2021UnspecifiedEmployee A
Vaccination02/03/2021ModernaEmployee B
Booster06/20/2022UnspecifiedEmployee B
Vaccination05/03/2022ModernaEmployee C
Vaccination02/03/2022ModernaEmployee D
Booster02/01/2022ModernaEmployee D
Vaccination02/01/2022ModernaEmployee E
Booster02/15/2022ModernaEmployee E

 

Within my report, I have a Date slicer that stores the selection of the end user within a measure shown below.

Selected Date = SELECTEDVALUE('Calendar'[Date])
 
Once a date is selected, I am needing to do the following:
  1. Determine the Max Effective Date for each Unique ID when the employee has completed a Vaccination and the Effective Date of the Vaccination is less than or equal to 14 days prior to [Selected Date].  For example, if someone chose 4/1/22 as the [Selected Date], I would be looking for the Max [Effective Date] per [Unique ID] where [Type] = "Vaccination" and [Effective Date] <= 3/18/22 (14 days prior to 4/1/22).  For the next steps, let's refer to this Variable or Measure [Max Vaccination Effective Date].
  2. Once [Max Vaccination Effective Date] is found, I need to find the Min Effective Date for each [Unique ID] where [Type] = "Booster" and [Effective Date] is greater than [Max Vaccination Effective Date].  For the next step, let's call this Variable or Measure [Min Booster Effective Date].
  3. From there, I need to count the number of employees who completed a vaccination at least 14 days prior to the [Selected Date] and have either a [Min Booster Effective Date] that is before the employee's [Max Vaccination Effective Date], have a [Min Booster Effective Date] that is on or after the [Selected Date], or have not yet received a booster.

Using the sample table for Employees, I would expect the total number to be 3 (Employee A, Employee B, and Employee D).

 

I have built measures using all kinds of Filters, and the calculations are taking too much processing power and time to populate a result.  If anybody knows of the most simplistic way of getting these calculations, that would be great.  Please let me know if there is more context that I need to provide to help better illustrate what it is I am trying to do.

1 ACCEPTED SOLUTION
Saap
Resolver II
Resolver II

Hi @joshua_dillon 

Try to do something like this:
1. Disconnect the time table from your fact table.

2. Use this measure to check if the employee has an effective date between choosen date and choosen date minus 14 days:

 

CHECK - Employee active 14 days prior selected date = CALCULATE(DISTINCTCOUNT('Table'[Unique ID]),
FILTER( VALUES( 'Table'[Effective Date] ), 'Table'[Effective Date] <= MAX( 'Calendar'[Date] ) ),
FILTER( VALUES( 'Table'[Effective Date] ), OR( 'Table'[Effective Date] >= (MIN( 'Calendar'[Date] )-14), ISBLANK( 'Table'[Effective Date] ) ) ) )
 
3. Use this measure to find Maximum Vaccination Date:
Max Vaccination Date = calculate(MAX('Table'[Effective Date]),'Table'[Type]="Vaccination")
 
4. Use this measure to find the Minimum Booster Date that is after Maximum Vaccination Date:
Min Booster Date = CALCULATE(MIN('Table'[Effective Date]), FILTER('Table','Table'[Type]="Booster"&&'Table'[Effective Date]>[Max Vaccination Date]))

 

5. Use this measure to check the items listed in your 3rd point:

TOTAL CHECK = IF(
'Table'[CHECK - Employee active 14 days prior selected date]=1 &&
([Min Booster Date]<[Max Vaccination Date] || [Min Booster Date]>=MAX('Calendar'[Date]) || [Min Booster Date]=BLANK())
,1,0)

6. Use this measure to correct the counting of the sum in a matrix:
TOTAL CHECK ADJ = IF(HASONEFILTER('Table'[Effective Date]), 'Table'[TOTAL CHECK],SUMX('Table',[TOTAL CHECK]))

 

7. Then put the TOTAL CHECK ADJ measure in a visual level filter and set it to be equal or greater than 1.

 

For example, setting the date to 10.02.2022 I got these employees listed:

Saap_0-1655884088942.png

 



Here you can find the test pbix file: https://filetransfer.io/data-package/NLwtgy9D#link
Please check if the solutions is ok.

View solution in original post

1 REPLY 1
Saap
Resolver II
Resolver II

Hi @joshua_dillon 

Try to do something like this:
1. Disconnect the time table from your fact table.

2. Use this measure to check if the employee has an effective date between choosen date and choosen date minus 14 days:

 

CHECK - Employee active 14 days prior selected date = CALCULATE(DISTINCTCOUNT('Table'[Unique ID]),
FILTER( VALUES( 'Table'[Effective Date] ), 'Table'[Effective Date] <= MAX( 'Calendar'[Date] ) ),
FILTER( VALUES( 'Table'[Effective Date] ), OR( 'Table'[Effective Date] >= (MIN( 'Calendar'[Date] )-14), ISBLANK( 'Table'[Effective Date] ) ) ) )
 
3. Use this measure to find Maximum Vaccination Date:
Max Vaccination Date = calculate(MAX('Table'[Effective Date]),'Table'[Type]="Vaccination")
 
4. Use this measure to find the Minimum Booster Date that is after Maximum Vaccination Date:
Min Booster Date = CALCULATE(MIN('Table'[Effective Date]), FILTER('Table','Table'[Type]="Booster"&&'Table'[Effective Date]>[Max Vaccination Date]))

 

5. Use this measure to check the items listed in your 3rd point:

TOTAL CHECK = IF(
'Table'[CHECK - Employee active 14 days prior selected date]=1 &&
([Min Booster Date]<[Max Vaccination Date] || [Min Booster Date]>=MAX('Calendar'[Date]) || [Min Booster Date]=BLANK())
,1,0)

6. Use this measure to correct the counting of the sum in a matrix:
TOTAL CHECK ADJ = IF(HASONEFILTER('Table'[Effective Date]), 'Table'[TOTAL CHECK],SUMX('Table',[TOTAL CHECK]))

 

7. Then put the TOTAL CHECK ADJ measure in a visual level filter and set it to be equal or greater than 1.

 

For example, setting the date to 10.02.2022 I got these employees listed:

Saap_0-1655884088942.png

 



Here you can find the test pbix file: https://filetransfer.io/data-package/NLwtgy9D#link
Please check if the solutions is ok.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors