Forum Discussion
MEASURE with condition using another table
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
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 )
5 Replies
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
Can you please just show the formula for this second case (related in calculated column)?
Thank you so much
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 )
