Filter before applying sumx

[alsm]

I am performing sumx with multiplication across two tables as follows

ResultUSD2 =

VAR ResUSD = SUMX(ResultsLCYTbl, ResultsLCYTbl[ResultInLCY]*CALCULATE(VALUES(FxTbl[Rate]), TREATAS(SUMMARIZE(FxTbl,FxTbl[Currency],FxTbl[Date]),ResultsLCYTbl[Currency],ResultsLCYTbl[Date])))

Return     ResUSD

This works fine!

but before I send this ResultsLCYTbl into SUMX I want to filter ResultsLCYTbl  by values in another table (HeaderTbl)

i.e. something on lines

ResultUSD3 =

VAR select_date = SELECTEDVALUE(EomDates[Date])

VAR filt_headers = SUMMARIZE(FILTER(HeaderTbl, [ValidFrom] <= select_date && [ValidTo] >= select_date), HeaderTbl[HeaderKey])

VAR filt_reslcy = CALCULATE(VALUES(ResultsLCYTbl[ResultInLCY]), FILTER(ResultsLCYTbl, ResultsLCYTbl[HeaderKey] in filt_headers))

VAR ResUSD = SUMX(ResultsLCYTbl, ResultsLCYTbl[ResultInLCY]*CALCULATE(VALUES(FxTbl[Rate]), TREATAS(SUMMARIZE(FxTbl,FxTbl[Currency],FxTbl[Date]),ResultsLCYTbl[Currency],ResultsLCYTbl[Date])))

Return     ResUSD

i.e. filt_resöcy is the correct table on which i want to apply sumx

how would i do that?

 

Header Table
HeaderKey	ValidFrom	ValidTo
H1	1Jan2022	31Dec2022
H2	15Feb2022	31Oct2022
H3	10Mar2022	15Sep2022
H_FebOnly	7Feb2022	25Feb2022
H_FebMar	25Feb2022	14Apr2022

Monthly Results Table
Date	Name	ResultInLCY	Currency	HeaderKey
Monday, 31 January 2022	A	25	EUR	H1
Monday, 28 February 2022	A	53	EUR	H1
Monday, 28 February 2022	B	92	GBP	H2
Monday, 28 February 2022	D	32	GBP	H_FebOnly
Monday, 28 February 2022	E	97	USD	H_FebMar
Thursday, 31 March 2022	A	74	EUR	H1
Thursday, 31 March 2022	B	12	GBP	H2
Thursday, 31 March 2022	C	45	AUD	H3
Thursday, 31 March 2022	D	12	GBP	H_FebOnly
Thursday, 31 March 2022	E	87	USD	H_FebMar
Saturday, 30 April 2022	A	53	EUR	H1
Saturday, 30 April 2022	B	74	GBP	H2
Saturday, 30 April 2022	C	98	AUD	H3

FX Table
Date	Currency	Rate
Monday, 31 January 2022	CAD	1.2
Monday, 31 January 2022	EUR	1.1
Monday, 31 January 2022	GBP	1.07
Monday, 31 January 2022	AUD	1.4
Monday, 31 January 2022	USD	0.9
Monday, 28 February 2022	CAD	1.3
Monday, 28 February 2022	EUR	1.034
Monday, 28 February 2022	GBP	1.6
Monday, 28 February 2022	AUD	1.3
Monday, 28 February 2022	USD	1

so basically 'Monthly Results Table'[ResultsInLCY] *'FxTable['Rate'] works after joining these tables using TREATAS. However, I want to 'pre-filter' ResultsInLCY with Header Table falling in date range.

[alsm · Accepted solution]

I figured it out!!

ResultUSD2 =

VAR select_date = MAX(EomDates[Date])

VAR filt_headers = SUMMARIZE(FILTER(HeaderTbl, [ValidFrom] <= select_date && [ValidTo] >= select_date), HeaderTbl[HeaderKey])

VAR filt_resultslcytbl = FILTER(ResultsLCYTbl, ResultsLCYTbl[HeaderKey] in filt_headers)

VAR ResUSD = SUMX(filt_resultslcytbl, [ResultInLCY]*CALCULATE(VALUES(FxTbl[Rate]), TREATAS(SUMMARIZE(FxTbl,FxTbl[Currency],FxTbl[Date]),ResultsLCYTbl[Currency],ResultsLCYTbl[Date])))

Return    

    ResUSD

for me what was a revelation was in line

VAR ResUSD = SUMX(filt_resultslcytbl, [ResultInLCY]*CALCULATE(VALUES(FxTbl[Rate]), TREATAS(SUMMARIZE(FxTbl,FxTbl[Currency],FxTbl[Date]),ResultsLCYTbl[Currency],ResultsLCYTbl[Date])))

using [ResultInLCY] directly in second part of SUMX, earlier i was trying to use it as

filt_resultslcytbl[ResultInLCY], which power bi complained