Forum Discussion

topazz11's avatar
topazz11
Icon for Helper III rankHelper III
4 years ago
Solved

Display Data with Montn

Hello,

I have a set of data with Actual Values in one column and Target Values in second column, for every month.
I cannot figure out how to display those 2 values for each corresponding month side by side showing zero value as well. 

For example: Value1 and Value2 for each month should be in one row but past month value from value1 (table1), current month value is greater between value1 and value2 and future month value from value2(table2) 

 

Thank you for your help

 

 

Table1  
IDDateValue1
A1001/1/2020100
A1002/1/2020 
A1003/1/2020200
A1004/1/2020 
A1005/1/2020 
A1006/1/2020300
A1007/1/2020100
A1008/1/2020 
A1009/1/2020 
A10010/1/2020 
A10011/1/2020 
A10012/1/2020 
A11101/1/2020400
A11102/1/2020200
A11103/1/2020200
A11104/1/2020 
A11105/1/2020 
A11106/1/2020300
A11107/1/2020300
A11108/1/2020 
A11109/1/2020 
A111010/1/2020 
A111011/1/2020 
A111012/1/2020 

 

 

Table2  
IDDateValue2
A1001/1/2020 
A1002/1/2020 
A1003/1/2020200
A1004/1/2020 
A1005/1/2020300
A1006/1/2020 
A1007/1/2020200
A1008/1/2020 
A1009/1/2020100
A10010/1/20201000
A10011/1/2020 
A10012/1/2020200
A11101/1/2020400
A11102/1/2020200
A11103/1/2020200
A11104/1/2020 
A11105/1/2020 
A11106/1/2020300
A11107/1/2020350
A11108/1/2020500
A11109/1/2020600
A111010/1/2020400
A111011/1/2020500
A111012/1/2020500
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi topazz11 ，

    Pls test the below dax to create  a new column on the table1:

    test = 
VAR test1 =
    IF (
        LOOKUPVALUE (
            Table2[Value2],
            Table2[Date], Table1[Date],
            Table2[ID], Table1[ID]
        )
            = BLANK (),
        0,
        LOOKUPVALUE (
            Table2[Value2],
            Table2[Date], Table1[Date],
            Table2[ID], Table1[ID]
        )
    )
VAR test2 =
    IF ( Table1[Value1] = BLANK (), 0, Table1[Value1] )
RETURN
    IF ( test1 > test2, test1, test2 )

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

1 Reply

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi topazz11 ，

    Pls test the below dax to create  a new column on the table1:

    test = 
VAR test1 =
    IF (
        LOOKUPVALUE (
            Table2[Value2],
            Table2[Date], Table1[Date],
            Table2[ID], Table1[ID]
        )
            = BLANK (),
        0,
        LOOKUPVALUE (
            Table2[Value2],
            Table2[Date], Table1[Date],
            Table2[ID], Table1[ID]
        )
    )
VAR test2 =
    IF ( Table1[Value1] = BLANK (), 0, Table1[Value1] )
RETURN
    IF ( test1 > test2, test1, test2 )

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien