MEASURE with condition using another table

[Specialist90]

Hello,

I need to create a MEASURE which sum all the Amounts lower than a certain date (like June 30, 2023) from TABLE 1, but dates in TABLE 1 are expressed in text, therefore I need to use the TABLE 2 where texts are associated with dates expressed as numbers.

Any help? 

TABLE 1

Amounts

Trade Date (text format)

TABLE 2

Trade Date (text format)

Date (number format)

Year

Month 

Days

[ToddChitt]

My Measure = CALCULATE ( SUM ( 'Table 1'[Amounts] ), RELATED ( 'Table 2'[Date] < "08/30/2023" ) )

If that doesn't work, you may need to create a calculated column using the RELATED funtion to bring the Date into Table 1

[Specialist90]

Can you please just show the formula for this second case (related in calculated column)?

Thank you so much

[ToddChitt · Accepted solution]

Create a calculated column in Table 1 as:

My Date Number Format From Table 2 = RELATED ( 'Table 2'[Date] )

RELATED function (DAX) - DAX | Microsoft Learn

My Measure = CALCULATE ( SUM ( 'Table 1'[Amounts] ), 'Table 1'[My Date Number Format From Table 2] < 20230630 ) 

[Specialist90]

I just get this error when using the formulas above:

[Specialist90]

Ok just solved adding "Value" to the formula