cancel
Showing results for
Did you mean:
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
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.

4 REPLIES 4
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.

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

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

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.

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### 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.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 289 members 3,281 guests
Recent signins: