Forum Discussion

Vatz8's avatar
Vatz8
Helper I
3 years ago
Solved

Using if statement with date condition to choose between two measures in different tables.

Hi,

I have two measures in two different table lets say Table 1 and Table 2. I have created a matrix table with Table 1 - Measure as value field. However, now we have received a data table (Table 2) which is similar to the previous one  and I have created measures similar to the previous table measures i.e. Table 2- Measure 2.

 

I need to add this measure to the same data matrix but the condition is that if the date is >01/20/2023 then I have to select Measure 2 and if it is before 01/20/2023 then the matrix selects Measure 1. I tried using simple if statement but it does not work as the condition part does not take date column it only takes measures.

 

Please let me know if there is a solution here.I cannot merge the two tables because even if they are similar they are not exactly the same.So will need to keep the two tables seperate.

 

Thank you in Advance.

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Vatz8 ,

     

    According to your statement, I think there should be both [Date] columns in 'Table 1' and 'Table 2'. Here I suggest you to create a DimDate table by CALENDAR() or CALENDARAUTO() function.

    Table1:

    Table2:

    Measure:

    Measure = 
VAR _ADD =
    ADDCOLUMNS (
        DimDate,
        "IF",
            VAR _DATE =
                DATE ( 2023, 01, 20 )
            RETURN
                IF ( DimDate[Date] > _DATE, [Measure 2], [Measure 1] )
    )
RETURN
    SUMX ( _ADD, [IF] )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Wheezy's avatar
    Wheezy
    Regular Visitor
    3 years ago

    What if you try the next measure:

     

    IF(

    SELECTEDVALUE([Date_Column]) > DATE(2023, 01, 20),

    [Measure2],

    [Measure1]

    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Vatz8 ,

     

    According to your statement, I think there should be both [Date] columns in 'Table 1' and 'Table 2'. Here I suggest you to create a DimDate table by CALENDAR() or CALENDARAUTO() function.

    Table1:

    Table2:

    Measure:

    Measure = 
VAR _ADD =
    ADDCOLUMNS (
        DimDate,
        "IF",
            VAR _DATE =
                DATE ( 2023, 01, 20 )
            RETURN
                IF ( DimDate[Date] > _DATE, [Measure 2], [Measure 1] )
    )
RETURN
    SUMX ( _ADD, [IF] )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.