Filter the results based on a date and grade field - only latest grade

[Kraftfood]

Hello community,

I have an issue with a dax formula. 

Please see my data:

Name	Grade	Week end date	Days Available	Days Worked	Weekly utilization
Resource 1	Grade 1	08/01/2023	5	4	80%
Resource 1	Grade 1	15/01/2023	5	2	40%
Resource 1	Grade 1	22/01/2023	5	4	80%
Resource 1	Grade 1	29/01/2023	5	5	100%
Resource 1	Grade 2	05/02/2023	5	2	40%
Resource 1	Grade 2	12/02/2023	5	5	100%
Resource 1	Grade 2	19/02/2023	5	4	80%
Resource 2	Grade 3	08/01/2023	5	5	100%
Resource 2	Grade 3	15/01/2023	5	5	100%
Resource 2	Grade 3	22/01/2023	5	5	100%
Resource 2	Grade 3	29/01/2023	5	5	100%
Resource 2	Grade 3	05/02/2023	5	5	100%
Resource 2	Grade 3	12/02/2023	5	4	80%
Resource 2	Grade 3	19/02/2023	5	0	0%
Resource 3	Grade 1	08/01/2023	5	2	40%
Resource 3	Grade 1	15/01/2023	5	5	100%
Resource 3	Grade 2	22/01/2023	5	4	80%
Resource 3	Grade 2	29/01/2023	5	5	100%
Resource 3	Grade 3	05/02/2023	5	5	100%
Resource 3	Grade 3	12/02/2023	5	5	100%
Resource 3	Grade 3	19/02/2023	5	5	100%

I use a calendar table linked to this "Week end date" field.

I calculate the utilization with a measure "divide(sum(days worked) , sum(days available))"

I use date filter the obtain the utilization split per period.

I want now to obtain the utilization based on the latest grade of the resource.

For example, for the resource 1, it would be only for the grade 2, so for the entire period: 11 / 15 = 73%. For the resource 3 it would be only for the grade 3: 15 / 15 = 100%...

I tried many measures, tried to add columns with the latest grade, ... but the results of the utilization are never correct. 

Anyone able to crack it please? 

[v-xinruzhu-msft]

Hi 

[Kraftfood]

Hi?

[v-xinruzhu-msft]

Hi @Kraftfood 

You can create a measure

Measure 2 = var a=MAXX(FILTER(ALLSELECTED('Table'),[Name] IN VALUES('Table'[Name])),[Grade])
var b=SUMX(FILTER(ALL('Table'),[Name] in VALUES('Table'[Name])&&[Grade]=a),[Days Available])
var c=SUMX(FILTER(ALL('Table'),[Name] in VALUES('Table'[Name])&&[Grade]=a),[Days Worked])
return DIVIDE(c,b)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kraftfood]

Hi v-xinruzhu-msft

Thanks a lot for your help, the results are almost what I wanted!

There is a slight difference between what the results should be and the outcome of the measure.

Resource A got promoted to a new grade the 01.07.2023. Total days worked = 31, total days available = 34. Utilization should be 91.18%, but the measure is giving 94.12%. There are no filters on a period. See utilization of resource A for 2023 and the results on the utilization measure below.

The utilization from the measure is sligthly off compared to the correct utilization for other resources that got promoted during the year and for all other resources.

I am not sure what would you need to investigate more, please let me know so I can share with you 🙂 

Best Regarde.

See below the detail of its available days and worked days.

Resource	Date	Grade	Days Available	Days Worked
Resource A	08/07/2023	New Grade	0	0
Resource A	15/07/2023	New Grade	2	2
Resource A	22/07/2023	New Grade	3	3
Resource A	29/07/2023	New Grade	0	0
Resource A	31/07/2023	New Grade	0	0
Resource A	05/08/2023	New Grade	0	0
Resource A	12/08/2023	New Grade	0
Resource A	19/08/2023	New Grade	0
Resource A	26/08/2023	New Grade	0
Resource A	31/08/2023	New Grade	0	0
Resource A	02/09/2023	New Grade	0	0
Resource A	09/09/2023	New Grade	0	0
Resource A	16/09/2023	New Grade	0	0
Resource A	23/09/2023	New Grade	0	0
Resource A	30/09/2023	New Grade	0	0
Resource A	07/10/2023	New Grade	0	0
Resource A	14/10/2023	New Grade	0	0
Resource A	21/10/2023	New Grade	0	0
Resource A	28/10/2023	New Grade	0	0
Resource A	31/10/2023	New Grade	0
Resource A	04/11/2023	New Grade	0	0
Resource A	11/11/2023	New Grade	0	0
Resource A	18/11/2023	New Grade	0
Resource A	25/11/2023	New Grade	0
Resource A	30/11/2023	New Grade	1	1
Resource A	02/12/2023	New Grade	4	4
Resource A	09/12/2023	New Grade	4	4
Resource A	16/12/2023	New Grade	5	5
Resource A	23/12/2023	New Grade	5	5
Resource A	30/12/2023	New Grade	5	3
Resource A	31/12/2023	New Grade	5	4
Total Resource A New Grade		34	31

[v-xinruzhu-msft]

Hi @Kraftfood 

The new  data you provided is different from the original data, i am confused that what does the new grade mean? you said you want to find the latest grade based on the date, how to filter it based on the "New grade"?

Best Regards!

Yolo Zhu

[Kraftfood]

Hi @v-xinruzhu-msft , sorry the new data provided was manually written. I have re-written the new data keeping the same format as the first post.

In the first post I created everything manually, in the screenshot below we see the actual days avaivable and days worked of a resource. Before the week end date 08.07.2023 the grade was "Grade 1"

Name	Week end date	Grade	Days Available	Days Worked	Weekly utilization
Resource 1	08/07/2023	Grade 2	0	0	0%
Resource 1	15/07/2023	Grade 2	2	2	100%
Resource 1	22/07/2023	Grade 2	3	3	100%
Resource 1	29/07/2023	Grade 2	0	0	0%
Resource 1	31/07/2023	Grade 2	0	0	0%
Resource 1	05/08/2023	Grade 2	0	0	0%
Resource 1	12/08/2023	Grade 2	0		0%
Resource 1	19/08/2023	Grade 2	0		0%
Resource 1	26/08/2023	Grade 2	0		0%
Resource 1	31/08/2023	Grade 2	0	0	0%
Resource 1	02/09/2023	Grade 2	0	0	0%
Resource 1	09/09/2023	Grade 2	0	0	0%
Resource 1	16/09/2023	Grade 2	0	0	0%
Resource 1	23/09/2023	Grade 2	0	0	0%
Resource 1	30/09/2023	Grade 2	0	0	0%
Resource 1	07/10/2023	Grade 2	0	0	0%
Resource 1	14/10/2023	Grade 2	0	0	0%
Resource 1	21/10/2023	Grade 2	0	0	0%
Resource 1	28/10/2023	Grade 2	0	0	0%
Resource 1	31/10/2023	Grade 2	0		0%
Resource 1	04/11/2023	Grade 2	0	0	0%
Resource 1	11/11/2023	Grade 2	0	0	0%
Resource 1	18/11/2023	Grade 2	0		0%
Resource 1	25/11/2023	Grade 2	0		0%
Resource 1	30/11/2023	Grade 2	1	1	100%
Resource 1	02/12/2023	Grade 2	4	4	100%
Resource 1	09/12/2023	Grade 2	4	4	100%
Resource 1	16/12/2023	Grade 2	5	5	100%
Resource 1	23/12/2023	Grade 2	5	5	100%
Resource 1	30/12/2023	Grade 2	5	3	60%
Resource 1	31/12/2023	Grade 2	5	4	80%
Total Resource A New Grade		34	31	91%

[Kraftfood]

See another example