Comparing two data sources into a PivotTable

JohnMcDoe — Thu, 11 Jun 2020 14:42:52 GMT

Dear community,

I've gotten pretty far with developing a dashboard of mine, but I seem to be stuck at something:

From data side I am loading two tables in which I'm importing current and last week data.
I built a pivot table to compare some figures from the two datasets above.
Problem is with the comparison itself, I can't seem to "marry" the two together. I can't "lookup" the same category in the historical table dynamically, only if I specifically specify it with a if condition. Categories are the same between the two tables:

The calculation formula I am using:

Sum({<LastFriday.CurrentStage = {'Assigned','Created'}>} if(LastFriday.MainCategory = MainCategory , [SalesValue1LastFriday]))

(Highlighted is the part that probably needs changing)

So in short, I need to match LastFriday.MainCategory (the MainCategory field from the LastFriday table) to the same field in the Pivot Table, which uses the non-historic MainCategory values). I tried to account for this with a simple if as seen above, but the data it spits is not accurate. If I manually specify the category:

Sum({<LastFriday.CurrentStage = {'Assigned','Created'}>} if(LastFriday.MainCategory = 'B' , [SalesValue1LastFriday]))

then it works flawlessly, it's just then applies it to every row, obviously.

topic Comparing two data sources into a PivotTable in App Development

Comparing two data sources into a PivotTable