Forum Discussion

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

Show values for each month

Hello,

I have a set of data with Actual Values in one column and Target Values in second column.
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

 

here is the sample data

pbix 

 

 

 

 

Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    Hi topazz11 

     

    There's a few ways you can do this both using DAX and PowerQuery but I chose to do the following:

     

    1. Create calculated column that returns a unique ID and add this to both your Table1 and Table2

    Unique = 

    VAR _1 = SUBSTITUTE(CONCATENATE(Table1[ID],Table1[Date]),"/",".")

    RETURN

    _1

    2. Create a calculated column that performs a lookup on the new Unique column: in your Table1

    Lookup = 

    VAR _1 = LOOKUPVALUE ( Table2[Future $] , Table2[Unique] , Table1[Unique] )

    RETURN

    _1

    3. Final calculated column for the conditional statement is:

    Check = 

    SWITCH ( 
        TRUE () , 
            Table1[Lookup] < Table1[Current $] , Table1[Current $] ,
            Table1[Lookup] = Table1[Current $] , Table1[Current $] ,
            Table1[Lookup] > Table1[Current $] , Table1[Lookup] , 
            0 )

    Your output is aligned to what you seek 🙂

     

    PBIX file also attached 🙂

     

    Hope this helps!

    Theo 🙂

     

1 Reply

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi topazz11 

     

    There's a few ways you can do this both using DAX and PowerQuery but I chose to do the following:

     

    1. Create calculated column that returns a unique ID and add this to both your Table1 and Table2

    Unique = 

    VAR _1 = SUBSTITUTE(CONCATENATE(Table1[ID],Table1[Date]),"/",".")

    RETURN

    _1

    2. Create a calculated column that performs a lookup on the new Unique column: in your Table1

    Lookup = 

    VAR _1 = LOOKUPVALUE ( Table2[Future $] , Table2[Unique] , Table1[Unique] )

    RETURN

    _1

    3. Final calculated column for the conditional statement is:

    Check = 

    SWITCH ( 
        TRUE () , 
            Table1[Lookup] < Table1[Current $] , Table1[Current $] ,
            Table1[Lookup] = Table1[Current $] , Table1[Current $] ,
            Table1[Lookup] > Table1[Current $] , Table1[Lookup] , 
            0 )

    Your output is aligned to what you seek 🙂

     

    PBIX file also attached 🙂

     

    Hope this helps!

    Theo 🙂