Forum Discussion
alsm
Helper III
Helper IIIFilter before applying sumx
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))
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.
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])))ReturnResUSDfor 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
1 Reply
alsmI 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])))ReturnResUSDfor 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