Forum Discussion

iiomarioii's avatar
iiomarioii
Icon for Helper II rankHelper II
6 years ago
Solved

Creating a new column with values from two other columns based on Date

Hi guys, 

 

this is my problem: 

I have a Date table and two following tables: 

Table 1

DateValue
01.02.2020 2€
01.03.2020 2€
01.04.20202€
01.05.20202€

 

Table 2 

01.04.2020 5€

01.05.2020

5€
01.06.20205€
01.07.20205€

 

I would like to create a new column that contains all values from Table 1 until 31.03.2020. From 01.04.2020 it should contain the values of Table 2. 

Wanted result: 

Date

Value

01.02.2020 

2€

01.03.2020

2€

01.04.2020

5€

01.05.2020

5€ 

01.06.2020

5€

01.07.2020

5€

 

I have tried this formula to get this column: 

New column = IF(SUMX('Date','Date'[Date])<= DATE(2020, 3,31) ,SUMX('Table1','Table1 '[Value]), SUMX('Table2','Table2'[Value] )) 
 
But obviously Power BI will only give me the values of Table 2 with this formula.

 

Thank you for your support 🙂 

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi  iiomarioii ,

     

    Create a new table using below dax expression:

     

    Table 2 = 
var _tablea=CALCULATETABLE('Table','Table'[Date]<=DATE(2020,3,31))
var _tableb=CALCULATETABLE('Table (2)','Table (2)'[Date]>=DATE(2020,4,1))
Return
UNION(_tablea,_tableb)

     

    And you will see:

    For the related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
     

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi iiomarioii ,

     

    Try the following code:

     

    Column =
IF (
    'Date'[Date] <= DATE ( 2020; 3; 31 );
    CALCULATE (
        SUM ( Table1[Value] );
        FILTER ( Table1; Table1[Date] = 'Date'[Date] )
    );
    CALCULATE (
        SUM ( Table2[Value] );
        FILTER ( Table2; Table2[Date] = 'Date'[Date] )
    )
)

     

    I'm assuming you are adding this column to the date table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi  iiomarioii ,

     

    Create a new table using below dax expression:

     

    Table 2 = 
var _tablea=CALCULATETABLE('Table','Table'[Date]<=DATE(2020,3,31))
var _tableb=CALCULATETABLE('Table (2)','Table (2)'[Date]>=DATE(2020,4,1))
Return
UNION(_tablea,_tableb)

     

    And you will see:

    For the related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!