Get values from a temporary table variable

[leolapa_br]

Below is a piece of the fact table 'fTrans' that provides context to my question.

Ticker	Date	Sale date related to respective purchase	Days	Transaction	Shares	Price	Purchase total
ABEV3	01/Mar/2021	04/Jun/2021	95	Purchase	77	$12.93	$995.61
ABEV3	04/May/2021	04/Jun/2021	31	Purchase	73	$13.60	$992.80
ABEV3	04/Jun/2021			Sale	150	$17.91
ABEV3	28/Jun/2021	29/Oct/2021	123	Purchase	62	$16.01	$992.62
ABEV3	29/Oct/2021			Sale	62	$15.62
ALSO3	07/Jan/2021	03/Feb/2021	27	Purchase	39	$25.12	$979.68
ALSO3	03/Feb/2021			Sale	39	$27.20
ALSO3	29/Jun/2021	30/Mar/2022	274	Purchase	36	$27.53	$991.08
ALSO3	07/Jan/2022	30/Mar/2022	82	Purchase	56	$17.85	$999.60
ALSO3	30/Mar/2022			Sale	92	$22.61
APER3	18/Nov/2021	22/Dec/2021	34	Purchase	33	$29.88	$986.04
APER3	22/Dec/2021			Sale	33	$33.40
APER3	14/Jun/2022	11/Aug/2022	58	Purchase	34	$29.14	$990.76
APER3	04/Aug/2022	11/Aug/2022	7	Purchase	42	$23.57	$989.94
APER3	11/Aug/2022			Sale	76	$30.33

As you can see for all rows whose [Transaction] column is a "Sale" the [Days] column has empty values.

The reason being that there are cases where a "Sale" transaction relates to more than one previous "Purchase" transaction, therefore a weighted average of [Days] needs to be calculated for those "Sale" rows.

So, my goal is to write up a measure that will provide a weighted average of [Days] for every [Transaction] "Sale" rows that are currently sitting empty, and in order to get there I started with this:

EVALUATE
VAR Supp_Tbl = 
	ADDCOLUMNS( 
		SUMMARIZE( 
			fTrans, 
			fTrans[Ticker], 
			fTrans[Date], 
			fTrans[Sale date related to respective purchase], 
			fTrans[Transaction], 
			"@Days", SUMX( 
				fTrans, 
				IF( 
					fTrans[Transaction] = "Sale", 
					BLANK(), 
					INT( fTrans[Sale date related to respective purchase] - fTrans[Date] ) 
				) 
			), 
			"@PurchTotal", [Purchase total] 
		), 
		"@WghtProdDaysPurch", [@Days] * [@PurchTotal] 
	)
VAR Days_Tbl = 
	ADDCOLUMNS( 
		GROUPBY( 
			Supp_Tbl, 
			fTrans[Ticker], 
			fTrans[Sale date related to respective purchase], 
			"@SumPurchTotal", SUMX( 
				CURRENTGROUP(), 
				[@PurchTotal] 
			), 
			"@SumWghtProdDaysPurch", SUMX( 
				CURRENTGROUP(), 
				[@WghtProdDaysPurch] 
			) 
		), 
		"@WghtdAvgDays", INT( 
				DIVIDE( 
				[@SumWghtProdDaysPurch], 
				[@SumPurchTotal] 
			) 
		) 
	)
RETURN
Days_Tbl

And it returns the following temporary table VAR "Days_Tbl" whose highlighted [@WghtdAvgDays] provides said weighted average of [Days] that I need for those "Sale" transaction rows:

Ticker	Sale date related to respective purchase	@SumPurchTotal	@SumWghtProdDaysPurch	@WghtdAvgDays
ABEV3
ABEV3	04/Jun/2021	1,988.41	125,359.75	63
ABEV3	29/Out/2021	992.62	122,092.26	123
ALSO3
ALSO3	03/Feb/2021	979.68	26,451.36	27
ALSO3	30/Mar/2022	1,990.68	353,523.12	177
APER3
APER3	22/Dec/2021	986.04	33,525.36	34
APER3	11/Aug/2022	1,980.7	64,393.66	32

Now all I need is to get those values from [@WghtdAvgDays] under VAR "Days_Tbl" whenever [Sale date related to respective purchase] matches fTrans[Date] under each [Ticker].

And for that I first tried by writing the following code:

LOOKUPVALUE( 
	[@WghtdAvgDays], 
	fTrans[Sale date related to respective purchase], 
	MAXX( 
		FILTER( 
			fTrans, 
			fTrans[Ticker] = EARLIER( fTrans[Ticker] ) && 
			fTrans[Date] = EARLIER( fTrans[Date] ) && 
			fTrans[Transaction] = "Sale" 
		), 
		fTrans[Date] 
	) 
)

But it right off the bat presents two issues:

1. The column 'fTrans[Sale date related to respective purchase]' either doesn't exist or doesn't have a relationship to any table available in the current context (message given by PBI when committing the measure code).
2. [@WghtdAvgDays] can't be used as the first argument of LOOKUPVALUE as it expects a fully qualified column (message provided by DAX Studio while attempting to troubleshoot the code).

How am I supposed to get the values from the [@WghtdAvgDays] column under the temporary table VAR "Days_Tbl" then?

Thanks in advance...

P.S.:

I also tried the route of creating a third temp table with SUMMARIZECOLUMNS but it ends up aggregating the overall total of [Days] for all rows:

FILTER( 
	SUMMARIZECOLUMNS( 
		fTrans[Ticker], 
		fTrans[Date], 
		fTrans[Transaction], 
		"@Days", CALCULATE( 
			SUMX( 
				Days_Tbl, 
				[@WghtdAvgDays] 
			), 
		fTrans[Sale date related to respective purchase] = fTrans[Date] 
		) 
	), 
	fTrans[Transaction] = "Sale" 
)

Ticker	Date	Transaction	Days
ABEV3	04/Jun/2021	Sale	30,080
ABEV3	29/Out/2021	Sale	30,080
ALSO3	03/Feb/2021	Sale	30,080
ALSO3	30/Mar/2022	Sale	30,080
APER3	22/Dec/2021	Sale	30,080
APER3	11/Aug/2022	Sale	30,080

[leolapa_br · Accepted solution]

@lbendlin your approach sure provided the stepping stones for helping me get across the finish line.

Thanks very much for your valuable input!

Just a couple of tweaks to your code were needed to reach the goal:

• [Days] and [Purchase total] are measures instead of columns of table 'fTrans', so all it was needed was to remove 'fTrans' from both within SUMX.
• Your calculation provides the equivalent in Excel of a SUMPRODUCT between [Days] and [Purchase total], so all it was needed in order to get a proper weighted average of [Days] was to divide that by the SUM of [Purchase total], and I did that by wrapping it around CALCULATE with the same filters (at least to me it somewhat looked odd a CALCULATE within another CALCULATE, but that's the only way I know how to do it for that particular case - perhaps creating a VAR for that would be more within the best practice guidelines).

So the final code looks as follows:

Table = 
	ADDCOLUMNS( 
		SUMMARIZE( 
			FILTER( 
				fTrans, 
				fTrans[Transaction] = "Sale" 
			), 
			fTrans[Ticker],
			fTrans[Date] 
		), 
		"WavgDays", 
		VAR Ticker_Ref = [Ticker]
		VAR Date_Ref = [Date]
		RETURN
			CALCULATE( 
				SUMX( fTrans,
					DIVIDE( 
						[Days] * [Purchase total], 
						CALCULATE( 
								[Purchase total], 
							ALL( fTrans ), 
							fTrans[Ticker] = Ticker_Ref, 
							fTrans[Sale date related to respective purchase] = Date_Ref 
						)
					)
				), 
				ALL( fTrans ), 
				fTrans[Ticker] = Ticker_Ref, 
				fTrans[Sale date related to respective purchase] = Date_Ref 
			) 
		)

Thanks!

[lbendlin]

I would run this the other way round, starting with the Sale dates.

I don't think you need a measure for this - the results don't seem to be impacted by user filter choices.

Table = ADDCOLUMNS(SUMMARIZE(FILTER(fTrans,[Transaction]="Sale"),[Ticker],[Date]),"WavgDays",var t = [Ticker] var d=[Date] return CALCULATE(sumx(fTrans,fTrans[Days]*fTrans[Purchase total]),all(fTrans),fTrans[Ticker]=t,fTrans[Sale date related to respective purchase]=d))

[leolapa_br]

@lbendlin 

I created a measure that would allow me to plop that [WavgDays] column from your calculated table in whatever visual I may need (on the case below, a simple table), but I always keep getting the overall total of days:

Ticker	Date	Days	Transaction	Shares	Price	Purchase total	Days average 1	Days average 2
ABEV3	01/Mar/2021	95	Purchase	77	$12.93	$995.61
ABEV3	04/May/2021	31	Purchase	73	$13.60	$992.80
ABEV3	04/Jun/2021		Sale	150	$17.91		29,360	29,360
ABEV3	28/Jun/2021	123	Purchase	62	$16.01	$992.62
ABEV3	29/Oct/2021		Sale	62	$15.62		29,360	29,360
ALSO3	07/Jan/2021	27	Purchase	39	$25.12	$979.68
ALSO3	03/Feb/2021		Sale	39	$27.20		29,360	29,360
ALSO3	29/Jun/2021	274	Purchase	36	$27.53	$991.08
ALSO3	07/Jan/2022	82	Purchase	56	$17.85	$999.60
ALSO3	30/Mar/2022		Sale	92	$22.61		29,360	29,360
APER3	18/Nov/2021	34	Purchase	33	$29.88	$986.04
APER3	22/Dec/2021		Sale	33	$33.40		29,360	29,360
APER3	14/Jun/2022	58	Purchase	34	$29.14	$990.76
APER3	04/Aug/2022	7	Purchase	42	$23.57	$989.94
APER3	11/Aug/2022		Sale	76	$30.33		29,360	29,360

I tried both these codes to no success.

Days average 1 = 
IF( 
    VALUES( fTrans[Transaction] ) = "Sale", 
    SUMX( 
        fWavgDays, 
        fWavgDays[WavgDays] 
    ) 
)
___________________________________________________________
Days average 2 = 
VAR Ticker_Ref = VALUES( fTrans[Ticker] )
VAR Date_Ref = VALUES( fTrans[Date] )
RETURN
CALCULATE( 
    IF( 
        VALUES( fTrans[Transaction] ) = "Sale", 
        SUM( fWavgDays[WavgDays] ) 
    ), 
    fTrans[Ticker] = Ticker_Ref, 
    fTrans[Date] = Date_Ref 
)

What am I doing wrong?

[lbendlin]

[Days] and [Purchase total] are measures instead of columns of table 'fTrans', so all it was needed was to remove 'fTrans' from both within SUMX.

why?

[leolapa_br]

Because both measures are located on another table I've got just to place the measures I create.

[lbendlin]

You will need to indicate what these measures do. You cannot convert a calculated column formula into a measure formula like that - you need to think deeply about the very different concepts, and most of the time you need to write a completely new measure from scratch. 

[leolapa_br]

@lbendlinI thought that once I came up with the calculated column I wanted to get, namely [WavgDays], I could create a measure using CALCULATE to iterate row by row through the table visual and pick up the value from [WavgDays] on the calculated table 'fWavgDays' to each "Sale" row on the table visual whose respective [Ticker] and [Date] match those on 'fWavgDays'.

Such measure would be something similar to a RELATED type process. Or talking in Excel terms, an INDEX/MATCH type deal, where the visual table is the destination and the source table on this case is 'fWavgDays'.

[lbendlin]

You cannot create a calculated column from measures (as you seem to be trying to do).  You may need to start over (as I mentioned above).

Please provide sample data that fully covers your issue. 

[leolapa_br]

@lbendlin I believe I didn't make myself clear and I apologize for that...

The table from the above reply that originated this follow-up question is not a calculated table, but just some sample rows from a table visual.

Below is a screenshot of the visual, which is basically a mirror of the table previously shown (I hadn't originally provided a screenshot because often times when I do screenshots on Stack Overflow I get frowned upon, so I guess I got bitten by the web shame snake...).

With that said, both columns in red "Days average 1" and "Days average 2" are not calculated columns on a calculated table, but measures dropped on a table visual whose codes are the ones previously provided.

So as I mentioned on my last note, once reaching my first goal of figuring out a way to calculate weighted average days for sales transactions, which you helped me do it by getting to [WavgDays], then all I needed to do after that was to come up with a measure that retrieves values from that calculated column [WavgDays], and that's what I'm trying to do now.

I thought CALCULATE could do that job since:

1. Together with a row iterator funcion should iterate row by row on whatever table we provide (on this case 'fWavgDays').
2. It therefore allows to relate to the calculated column [WavgDays] on its first expression argument.
3. Narrows down to the matching [Ticker] and [Date] on its second filter argument.

But I'm either doing something wrong with CALCULATE that's instead providing the overall total of days for every row, or CALCULATE shouldn't be used at all and instead I should take another route. Either way, I was hoping you could help me point to the right direction here...

Thanks for your patience and willingness to help!

[lbendlin]

After you create the calculated table it will not automatically be included in the data model. It is a "disconnected" table.  That is the reason why you see the same number all over the column/measure.  Wire the table into your data model as needed.

[leolapa_br]

@lbendlin that's the thing though, prior to creating that measure I did make sure to connect 'fWavgDays' calculated table to the the data model via a one-to-many relationship coming from the date table 'dDate'[Data] as shown below:

And looking at your originally suggested code, which I repost below, it looks like the date table 'dDate' is not being referenced, which makes sense since you didn't know I had a date table in my model. Do you think that could be the issue? And if so, should the below code be re-written or should I wire 'fTrans' to the calculated table 'fWavgDays' via [Date]? Such connection would have to be a many-to-many relationship so I'm not sure if that could bring up other issues to the model...

Table = ADDCOLUMNS(SUMMARIZE(FILTER(fTrans,[Transaction]="Sale"),[Ticker],[Date]),"WavgDays",var t = [Ticker] var d=[Date] return CALCULATE(sumx(fTrans,fTrans[Days]*fTrans[Purchase total]),all(fTrans),fTrans[Ticker]=t,fTrans[Sale date related to respective purchase]=d))

Thanks!

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Mention everything that is related to the issue. Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[leolapa_br]

@lbendlin please find below the link to the shared .pbix file related to this issue. Please let me know in case it doesn't work.

Investm_Import_Ctrl_TBD.pbix

I tried resetting the connection between 'fWavgsDays' calculated table and the rest of the data model via 'fTrans' with a many-to-many relationship from/to both [Date] columns (also had to get rid of the one-to-many connection with the date table in order to make the newly created connection active).

And it still didn't work as per the following screenshot (in blue are the hardcoded correct results). Now it's apparently summing up [WavgDays] for each [Date] for all [Ticker], so it looks like the correct connection setup is the one-to-many between 'fWavgsDays' and 'dDate' and the calculated table code somehow needs to reference 'dDate'[Data].