cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mb0307
Post Patron
Post Patron

Combine two tables based on Current Month

Hi I have two tables:

 

Table1

Month Year  |  Sales
Jan-201000
Feb-202000
Mar-20500

 

 

Table2

Month Year  |  Projections
Jan-20500
Feb-202000
Mar-201000
Apr-205000

 

Let's say MARCH is current month, I want a measure to provide result so that all previous months are taken from Table1 and current month and onwards data comes from Table2, example below:

 

RESULT

Month Year  |  Projections
Jan-201000
Feb-202000
Mar-201000
Apr-205000

 

Thanks for your help.

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @mb0307 , 

Sorry for my late reply.

You could use SUMMARIZE() to select columns when there are different number of columns between tables :

Table 3 =
VAR t1 =
    FILTER (
        SUMMARIZE ( 'Previous', [  Sales], Previous[Month Year  ] ),
        MONTH ( Previous[Month Year  ] ) < 3
    )
VAR t2 =
    FILTER (
        SUMMARIZE ( 'After', [  Projections], 'After'[Month Year ] ),
        MONTH ( 'After'[Month Year ] ) >= 3
    )
RETURN
    UNION ( t1, t2 )

Then the new table will be like this:

8.12.fo.PNG

Did I answer your question ? Please mark my reply as solution.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

View solution in original post

4 REPLIES 4
Eyelyn9
Community Support
Community Support

Hi @mb0307 , 

Sorry for my late reply.

You could use SUMMARIZE() to select columns when there are different number of columns between tables :

Table 3 =
VAR t1 =
    FILTER (
        SUMMARIZE ( 'Previous', [  Sales], Previous[Month Year  ] ),
        MONTH ( Previous[Month Year  ] ) < 3
    )
VAR t2 =
    FILTER (
        SUMMARIZE ( 'After', [  Projections], 'After'[Month Year ] ),
        MONTH ( 'After'[Month Year ] ) >= 3
    )
RETURN
    UNION ( t1, t2 )

Then the new table will be like this:

8.12.fo.PNG

Did I answer your question ? Please mark my reply as solution.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

View solution in original post

Eyelyn9
Community Support
Community Support

Hi @mb0307 ,

According to my understanding, you want to combine two tables based on current month-year , right?

You could use the following formula:

 

combine =
UNION (
    FILTER ( 'Previous', 'Previous'[Month Year  ].[MonthNo] < 3 ),
    FILTER ( 'After', 'After'[Month Year ].[MonthNo] >= 3 )
)

My visualization looks like this:

8.12.5.png

Best Regards,

Eyelyn Qin

@Eyelyn9   Thanks for you response but I am getting this error: "Each table argument of 'UNION' must have the same number of columns."

 

My two tables have different set of columns.  I want Sales and Projections union resulted in a same column please.

 

Thanks

Any help with this query will be much appreciated.  

 

Thanks.

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!