Reply
Regular Visitor
Posts: 26
Registered: ‎08-01-2017
Accepted Solution

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?


Accepted Solutions
New Contributor
Posts: 548
Registered: ‎08-03-2016

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

View solution in original post


All Replies
New Contributor
Posts: 548
Registered: ‎08-03-2016

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
Regular Visitor
Posts: 26
Registered: ‎08-01-2017

Re: Subtract columns of two tables of different length

[ Edited ]

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
Posts: 26
Registered: ‎08-01-2017

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
Posts: 548
Registered: ‎08-03-2016

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