Subtract columns of two tables of different length

[jomikk]

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?

[CahabaData]

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.

[jomikk]

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]

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 · Accepted solution]

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.