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
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!