Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
MeadeMan246
Frequent Visitor

Create a table from other table columns where there is common date field (Month)

Hi Guys,

 

I am a Power BI newbie, so bear with me please. 

 

I have created two summary tables: Monthly Revenues and Monthly Expenses, from two other tables, Revunues and Expenses, which had multiple daily entries.

 

MonthMonthly  Revenues
May200
June300
July500

Table - Monthly Revenues

 

MonthMonthly  Expenses
May50
June75
July200

 Table Monthly Expenses

 

I am having a challenge create a new table, from the two summary tables, where it would look like this:

 

MonthMonthly  RevenuesMonthly  Expenses
May20050
June30075
July500200

Table Monthly Margins 

 

I tried several solution posts, from related subject posts, but none quite lead to the desired table above.  I believe it is possible with DAX commands...right?

 

 

 

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

Hi @MeadeMan246 ,

 

You can create this table with this code:

Margins = 
ADDCOLUMNS(
    DISTINCT(
            UNION(
                SELECTCOLUMNS(Expenses, "Month", Expenses[Month]), 
                SELECTCOLUMNS(Revenues, "Month", Revenues[Month])
            )
    ),
    "Revenues", CALCULATE(SUM(Revenues[Monthly  Revenues]), Revenues[Month] = EARLIER([Month])),
    "Expenses", CALCULATE(SUM(Expenses[Monthly  Expenses]), Expenses[Month] = EARLIER([Month]))
)
 
Capture.PNG


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

Proud to be a Super User!



View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@MeadeMan246 , refer the power query way

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

or

Dax way

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
camargos88
Community Champion
Community Champion

Hi @MeadeMan246 ,

 

You can create this table with this code:

Margins = 
ADDCOLUMNS(
    DISTINCT(
            UNION(
                SELECTCOLUMNS(Expenses, "Month", Expenses[Month]), 
                SELECTCOLUMNS(Revenues, "Month", Revenues[Month])
            )
    ),
    "Revenues", CALCULATE(SUM(Revenues[Monthly  Revenues]), Revenues[Month] = EARLIER([Month])),
    "Expenses", CALCULATE(SUM(Expenses[Monthly  Expenses]), Expenses[Month] = EARLIER([Month]))
)
 
Capture.PNG


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

Proud to be a Super User!



Hi @ camargos88,

 

Your suggestion included the summarization of the daily entries for expenses and revenues, which I don't need to do because I have already created those two summary tables (for proofing/sanity checks).  

 

I tried modifying the rest of the ADDCOLUMNS argument, using different functions but with no success. Here is an example using VALUES to populate the cells but got an error.  

 
Margins =
ADDCOLUMNS(
DISTINCT(
UNION(
SELECTCOLUMNS('Monthly Expenses', "Month", 'Monthly Expenses'[Month]),
SELECTCOLUMNS('Monthly Revenues', "Month", 'Monthly Revenues'[Month])
)
),
"Revenues", VALUES('Monthly Revenues'[Monthly Revenues]),
"Expenses", VALUES('Monthly Expenses'[Monthly Expenses])
)
 
Error: A table of multiple values was supplied where a single value was expected.

 

 

 

 

Hi @MeadeMan246 ,

 

You have to summarize them again:

 

"Revenues", VALUES('Monthly Revenues'[Monthly Revenues]),
"Expenses", VALUES('Monthly Expenses'[Monthly Expenses])
 
 
The VALUES function only returns the distinct values for the column. So, you are trying to return the distinct values for each row..... you need 1 scalar value per row...
 
That's why you need the sum.


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

Proud to be a Super User!



 Hi camargos88 ,

 

Following your advice, I got the desired result. Thank you for your assistance and patience.

 

BR,

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!