Forum Discussion

Specialist90's avatar
Specialist90
Frequent Visitor
2 years ago
Solved

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...
Need Help
  • ToddChitt's avatar
    ToddChitt
    2 years ago

    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 ) 

ToddChitt's avatar
ToddChitt
Super User
2 years ago

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's avatar
    Specialist90
    Frequent Visitor
    2 years ago

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

     

    Thank you so much

    • ToddChitt's avatar
      ToddChitt
      Super User
      2 years ago

      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's avatar
        Specialist90
        Frequent Visitor
        2 years ago

        I just get this error when using the formulas above: