cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jomikk Regular Visitor
Regular Visitor

Subtract columns of two tables of different length

Hi, I have basically two tables. For the first table I have appended data from last day, last week, last 30 days and last 90 days. And for the other table I have data for today. So the today data is 96 rows long, and the historical comparison table is 96*4=384 rows long.

 

For the historical data I have a column identifying "Last Day", "Last Week", "Last 30 Days" and "Last 90 Days"

 

How can I subtract the historical data from the todays data? I want to take today-last day, today-last week, etc.

 

Is this possible?

1 ACCEPTED SOLUTION

Accepted Solutions
CahabaData New Contributor
New Contributor

Re: Subtract columns of two tables of different length

if the 2 tables share a common ID value (which is unique in a table and doesn't repeat) then you would do a join on that field between the tables.  then you can do math with any fields of these tables together.  You'll want to refer to the RELATED function in your DAX statement.

www.CahabaData.com
4 REPLIES 4
CahabaData New Contributor
New Contributor

Re: Subtract columns of two tables of different length

It should be do-able using dax measures.  Make a measure that is sum for Today, A measure that is sum for Last Day.  A measure that is sum for Last Week. etc.

 

That Sum statements I can't say not knowing your data structure but will all be identical except where it includes a filtering phrase like .... ID = "Last Day"  

 

Once those measures exist - and they should all exist stand alone; then another measure working with these measures is very straight forward.

 

this at least is the high level idea - - you would want to post some sample records for the community to view if you need a dax statement crafted.

 

www.CahabaData.com
jomikk Regular Visitor
Regular Visitor

Re: Subtract columns of two tables of different length

Thanks, but I don't want to subtract the sum, I want to subtract the column row wise. So I basically want to subtract the today table with historical table. This is do able when all tables are separate, but how can I do this when all historical are appended into one?

 

If it is easier I can also append the today/baseline into the other table, such that all tables are in one

jomikk Regular Visitor
Regular Visitor

Re: Subtract columns of two tables of different length

Temporary solution is to merge the baseline and each historical table, then do the subtraction and then append all together. But this is not optimal since I have to do an extra step

CahabaData New Contributor
New Contributor

Re: Subtract columns of two tables of different length

if the 2 tables share a common ID value (which is unique in a table and doesn't repeat) then you would do a join on that field between the tables.  then you can do math with any fields of these tables together.  You'll want to refer to the RELATED function in your DAX statement.

www.CahabaData.com

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 289 members 3,281 guests
Please welcome our newest community members: