subtract one table from another to achieve pick up figure

[pclarke]

I have a database that is receiving a daily extract of all orders which we use for several things including visualising all forward orders segmented in several different ways.

What i'd like to do is visualise the variance between the extract from two different days to see what has picked up. The daily extracts are denoted by as_at_date values and i've been able to get a filtered table of just today's extract using  TodayHoldings = FILTER('extract', 'extract'[as_at_date]=today()) and been able to filter yesterday's extract using  YesterdayHoldings = FILTER('extract', 'extract'[as_at_date]=today()-1).

Now what i'd like to do is subtract YesterdayHoldings from TodayHoldings and maintain the segmentation (each table is 57 columns so it needs to be able to be sliced in 56 different ways). I'm able to get a static number via  units_picked_up = CALCULATE(SUM(LatestHoldings[units_sold]))-CALCULATE(SUM('YesterdayHoldings'[units_sold])) but if i want to see what date all of these new orders are due i just get the total units_picked_up on each date.

I don't mind if it's individual columns that have to be calculated or if i can do it in the visualisation itself or if i need to create another table.

Any help would be greatly appreciated.

[mahoneypat]

I recommend you append your two tables together and do an easier DAX expression to get your difference from previous day.  You still could have all your slicers (although that is a lot), and those slicers would work on "both" sets of data (as they would now be in one table).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[pclarke]

Thank you for your reply @lbendlin. I'm not sure if that will work, my fault i forgot to mention that some of the lines may be amended, not nessesarily only new or deleted rows.

Thank you @mahoneypat, that sounds promising, could you point me in the direction of what statements you would think I'd need to read up on more to be able to achieve this? I know it's a lot of different columns, many of them are booleans used to filter the data so i probably shouldn't be referring to them as slicers but they do need to be preserved to achieve the desired outcome because different users will want to drill down into different sections of the data.

[pclarke · Accepted solution]

I've pretty much fixed it. I noticed that it was working with slicers that had their own table with a relationship to all three data tables. For example location has a location id and then a location table with both the id and name. But if it was a column that was in the extract table it wouldn't filter like the 'due_date' of an order.

So i created a table that has all the unique vales from the 'due_date' column and created a relationship between it, the extract table and the two filtered tables and it worked! Not the simplest method and i don't fully understand why i couldn't just create a relationship between the extract table and the two filtered tables on the 'due_date' column but if it works, it works.

[lbendlin]

if there are no duplicate rows in either of your tables then a simple EXCEPT() will give you the rows that are present in one table but not the other.  Depending if you need to handle deletions as well you may need to do the EXCEPT() twice, once in each direction (yesterday vs today, and today vs yesterday)