Forum Discussion
Iteration inside a Measure with ADDCOLUMNS & CALCULATE
Yourpattern suggestion is the rigth aproach, however, I was having conflicting issues with the ALLEXEPT when including it in the SUMMARIZE. The solution was to create a CALCULATEDTABLE with the ALLEXCEPT and use that table in the SUMMARIZE
Hi Ashish, thanks for your responce... this set of data should work:
| account_id | myyear | amount_converted |
| a1 | 2021 | $9,867. |
| a1 | 2021 | $10,506. |
| a2 | 2021 | $24,064. |
| a2 | 2022 | $17,928. |
| b1 | 2021 | $2,901. |
| b1 | 2022 | $31,824. |
| b1 | 2022 | $1,500. |
| b2 | 2021 | $441. |
| b2 | 2021 | $17,360. |
| b2 | 2022 | $18,677. |
The result in Power BI should be a single value of the total sum of the revenue retained in the "Result Column" = $38,630 shown below
| Unique Accounts | Amount in last period | Amount Current Period | Result |
| #=unique() | sum if in 2021 | sum if in 2022 | #=IF(last period>=curr period,curr period,last period) |
| a1 | 20373 | 0 | 0 |
| a2 | 24064 | 17928 | 17928 |
| b1 | 2901 | 33324 | 2901 |
| b2 | 17801 | 18677 | 17801 |
The calculation should first agregate the amounts by company for the previous period and the current period (periods are always current period = trailing 12 months previous period 13-24 months ago, so I just removed all filters and used varibles to assign a new filter context, see below)
VAR mindate =
EOMONTH(
MAX(fac_cs_churn_renewables[close_date]),-12)
VAR maxdate =
EOMONTH(
MAX(fac_cs_churn_renewables[close_date]),0)
VAR prevmindate =
EOMONTH(
MAX(fac_cs_churn_renewables[close_date]),-24)
and then I performed the calculation shown in the original question posted.. Thanks
ps: actually, if you tried this code to produce a table it should work, but it's not working on the measure
Super UserHi,
I believe Greg has already answered your question.