Compare Value of amount from 2 unrelated tables by column

[Anonymous]

hey, 

I will try my best to explain my situation here, so if you have any questions, please ask.

we have PBIX that we're working on under IMPORT mode. 

we have 2 fact tables:

1 - Revenue

2- FACT_INVOICE (2) (same as revenue) 

they are connected to the same Dim's but not to the same Dim Date table 

so Revenue connected to Dim_Date 

and FACT_INVOICE (2) connected to Dim_Date 2

why?

because we want to compare periods 

so we have 2 periods to choose from and we have a table that aggregates the data

under this, we have a table that does not connect to anything, with values:

now, for each "Model" (item) on the aggregated table, we need to determine if the item is whether "New" or "Old" (ignore "both") 

New = The item sold in Period 1 but NOT in Period 2 

Old =  The item Sold in Period 2 but NOT in Period 1 

this is the Calculation for this:

Old & New = if('Table'[Measure] = "New", "Old & New" , if('Table'[Measure] = "Both", "Both",BLANK()))

New Vs Old M = if(

[Sum Amount USD Invoice] > -999999999999 && [Sum Amount USD Invoice 2] <= 0 || [Sum Amount USD Invoice 2] > -999999999999 && [Sum Amount USD Invoice] <= 0 , "New",

if([Sum Amount USD Invoice] > -999999999999 && [Sum Amount USD Invoice 2] > -999999999999 , "Both"))

the measure works as expected, and the results are fine. 

however, the measure is only working while in a table with the "Model" column from Dim_Item Table. 

if it's removed (the "Model" column) from the table, the measure cant decide whether it's old/new.

I need to create a measure of "New Impact" and "Old Impact" but I don't know how to calculate that, as the New/Old attribute is not related to any table, it's just logical. 

so when I'm trying to do something like: Calculate(Sum(amount), Measure = "Old") it's just calculating the whole revenue of the selected period because "Old" is just a logical attribute and there's no "Model" in the context. 

I would like to know if I can calculate the old impact and new impact, in a card, not in a table, without the need for the "Model" column 

New Impact = The item sold in Period 1 but NOT in Period 2 

Old Impact =  The item Sold in Period 2 but NOT in Period 1 

just a couple of pics:

this is New Impact inside the table with model column (right results):

when I remove the model column (bad results, calculating the whole period amount and not only  new):

[ValtteriN]

Hi,

I am not 100% I understood your problem specifically, but from what I can tell I have had similar cases where I wanted to create a category measure and use this to make calculations. Typically this is in the context of customer categories e.g. "New customer","Old customer"...

The typical way that I use to solve these kinds of problems is to first create category measure (in your case [Old & New]). 

Now we  can use this category measure combined with SUMX, or e.g. COUNTX to make our calculations. Here is an example:

Category measure:

Category = IF(SELECTEDVALUE(TotalToZero[Subaccount])="c","c","")

C revenue =

SUMX(filter(TotalToZero,[Category]="c"),TotalToZero[Value])

End result:

By using this logic we can combat this issue:
"so when I'm trying to do something like: Calculate(Sum(amount), Measure = "Old") it's just calculating the whole revenue of the selected period because "Old" is just a logical attribute and there's no "Model" in the context. "

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!