Make calculations with Records in different tables with latest Date in Direct Query

[jvalencia]

I need help with this and really appreciate your inputs:

Main Objective: Calculate a compliance percentage per group, based on the follwing data:

- from Table 1, get the latest date for the P/N involved (I colored in  RED the one that I need)

- Then, evaluate if the date is within the past 30 Days (I bold the data that it meets the criteria, assuming today is 04/05/2018))

   The evaluation will be made based on the past 30 day from Today().

TABLE 1
P/N	DATE	PRODUCTION	LIFE TO DATE
ID-001	1/10/2018	1000	1000
ID-001	2/8/2018	1500	2500
ID-001	3/10/2018	1000	3500
ID-023	1/10/2018	1256	1256
ID-023	2/8/2018	1756	3012
ID-023	3/16/2018	1256	4268
ID-155	2/6/2018	1512	1512
ID-155	3/3/2018	2012	3524
ID-234	1/1/2018	2000	2000
ID-234	3/10/2018	1000	3000
ID-356	3/1/2018	1000	1000

TABLE 2
GROUP	P/N	NAME	LOCATION
A	ID-001	PART ABC	US
A	ID-023	PART CDF	US
A	ID-155	PART FGR	US
B	ID-234	PART HHH	MX
B	ID-356	PART AAA	MX

So using the Table 2, where the main info for the P/N is contained , start having a calculation for the compliance, for every GROUP that exists.

at the end, something like table would be Needed :

Can you shed some light on me?

Important: I am using Direct Query...

Group	Total Records	in compliance	%
A	3	2	66.7%
B	2	1	50.0%
total	5	3	60.0%

[Phil_Seamark]

HI @jvalencia, So what is the criteria to determine if an item in a group is not in compliance?

[jvalencia]

that is past of the 30 dasys from today()

This will be rolled up every day.

So lets say that the last date for an Item is in 29th day from now...the item still ok (For today and for tomorrow)

If Item_DATE >= (Today()-30) then 1 else 0

So by having this, I would like to have the overall performnace for the group

If 2 out of 3 items are ok, than the compliance is 2/3 = 66%

I hope I made myself clear

[Phil_Seamark]

Aren't all your items compliant according to your sample data?  I can't see how either group is not 100%

[jvalencia]

the Item ID-155 has 2 dates (Table 1)

         ID-155    2/6/2018

         ID-155    3/3/2018

  the first criteria is to select the latest date, therefore the one that is going to be analized is the item with date of 3/3/2018

   the days between today(4/5/2018) and 3/3/2018 is 33 day, so this Item will not be compliant

Doing the same for the other 2 items that belongs to GROUP A (ID-001 and ID-023) these are in compliance

       ID-001    latest date= 3/10/2018    which is 26 days from today which is OK (Less than 30 days)

       ID-023    latest date= 3/16/2018    which is 20 days from today which is OK

So for Group A , which have 3 items in total (Per table 2) the compliance will be 2 out of 3, which is 66%

Let me know if I clarify it...Thanks for your support

Note: I am using Direct Query, so not all the measures works in this environment :(, that is why I start looking for help.....

[Phil_Seamark · Accepted solution]

HI @jvalencia

I don't use Direct Query enough to remember what works and what doesn't.

Please try these three calculated measures and let me know what works.  I can help tweek the measures that don't work with DQ

Total Records = COUNTROWS('Table2')

In Compliance = 
	CALCULATE(
		DISTINCTCOUNT(
			'Table1'[P/N]),
			FILTER(
				'Table1',
				'Table1'[DATE]>=TODAY()-30)
				)

% = DIVIDE([In Compliance] , [Total Records])

This gives me this result on an Import model

[jvalencia]

Thanks So Much...It worked great!!

Kudos to you