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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors