Check scale based on most recent minor date

[nok]

Hello!
I have two tables that follow this structure:

ProductsTable

ID	RequestDate	Amount
111	23/06/2025	1000
111	24/06/2025	1010
111	19/07/2025	300
222	11/10/2025	500
333	20/12/2025	600

ScaleTable

ID	StartDate	Scale
111	20/06/2025	1000
111	15/07/2025	500
111	30/09/2025	200
222	27/08/2025	500
222	22/12/2025	300
333	30/10/2025	100

The two tables are related by the ID column. I want to create a new column in the ProductsTable that shows whether the Amount of products requested is in the correct scale for that particular product, based on the most recent date that is less than the product date. For example, in the example tables above, the frist product row ID is 111 and the RequestDate is 23/06/2025.
In the scale table, ID 111 has three scales:

ID	StartDate	Scale
111	20/06/2025	1000
111	15/07/2025	500
111	30/09/2025	200

However, the correct scale to count for this specific ID is scale 1000, as its date (20/06/2025) is less than the RequestDate  for this product (23/06/2025) and the next date is greater (15/07/2025) than the RequestDate. To check if the scale is correct, simply check if the Amount value is equal to Scale, or equal to multiples of that scale.
For example, since the scale of this product ID and date is 1000, if the product had an Amount of 2000, 5000 or 10000 it would also be in the scale, since these values ​​are multiples of the original scale (1000). However, the second row of this product (which has amount 1010) is not in the scale, since 1010 is not a multiple of 1000.

The final result should be something like this:

ID	RequestDate	Amount	InScale?
111	23/06/2025	1000	Yes
111	24/06/2025	1010	No
111	19/07/2025	300	No
222	11/10/2025	500	Yes
333	20/01/2025	600	No

How can I do this new column?

[V-yubandi-msft]

Hi @nok ,

Thanks for reaching out to the Fanbroc Community. @Ashish_Mathur , @Demert  has shared a solution that looks spot on with what you’re aiming for. Feel free to go through the suggestions, and if you have any questions or need further tweaks, we’re here and happy to help.

Big thanks to @Ashish_Mathur , @Demert  for the helpful input.

— Yugandhar
Community Support Team.

[Ashish_Mathur]

Hi,

Try this calculated column formula

Column = if(ISBLANK(CALCULATE(MIN(Scale[StartDate       ]),FILTER(Scale,Scale[ID      ]=EARLIER(Products[ID])&&Scale[StartDate       ]<=EARLIER(Products[RequestDate  ])&&mod(Scale[Scale   ],EARLIER(Products[Amount]))=0))),"No","Yes")

Hope this helps.

[Demert · Accepted solution]

Hi @nok ,

Following dax code for a new calculated column should work :

InScale = 
VAR _A = SUMMARIZE(FILTER(ScaleTable,ScaleTable[ID]=EARLIER(ProductTable[ID])), ScaleTable[ID],"@MaxDate", CALCULATE(MAX(ScaleTable[StartDate]),   ScaleTable[StartDate]< EARLIER(ProductTable[RequestDate])))

VAR _MaxDate = MAXX(_A,[@MaxDate])

VAR _Scale= CALCULATE(MAX(ScaleTable[Scale]), _MaxDate= ScaleTable[StartDate])
RETURN IF(_Scale = ProductTable[Amount] , TRUE() , FALSE())

We are creating a virtual table (summarize) of the scale table to only match the selected ID of the row in the product table where the max start date is before the requested date. Then we calculate the Scale of that date and look if the amount matches