- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Subtract columns of two tables of different length

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2017 06:55 AM

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?

Solved! Go to Solution.

Accepted Solutions

## Re: Subtract columns of two tables of different length

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-10-2017 03:10 PM

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.

All Replies

## Re: Subtract columns of two tables of different length

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2017 03:19 PM

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.

## Re: Subtract columns of two tables of different length

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2017 11:44 PM - edited 08-09-2017 11:59 PM

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

## Re: Subtract columns of two tables of different length

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-10-2017 01:09 AM

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

## Re: Subtract columns of two tables of different length

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-10-2017 03:10 PM

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.