Iteration inside a Measure with ADDCOLUMNS & CALCULATE

[David_S]

Hi BI Users,

 Interesting question which may be lacking some conceptual understanding 🙄

I need to measure the Net Revenue Retention, we define it as the amount retained by a customer over a period of time withouth including upsales, so if a client purchased 100 on the previous period and purchased 150 in the current period we only consider the 100 as a retained amount. 

So my aproach to the problem is... add a column to a table with key = the DISTINCT CompanyID which will sum the amount purchsed in the last period and another column with the amount purchased in the current period to then use the logic above. Below is the code for the DISTINCT. (source code for some variables like "prevmindate" is not relevant)

Var mytable = 
    ADDCOLUMNS(
        DISTINCT(fac_cs_churn_renewables[account_id]),
        "Prev Period",
        CALCULATE(SUM(fac_cs_churn_renewables[amount_converted]),
            ALLEXCEPT(  fac_cs_churn_renewables,
                        fac_cs_churn_renewables[close_date],
                        fac_cs_churn_renewables[account_id],
                        fac_cs_churn_renewables[account_segment]
            ),
            fac_cs_churn_renewables[close_date] > prevmindate && fac_cs_churn_renewables[close_date] <= mindate
        ),
        "Curr Period",
        CALCULATE(SUM(fac_cs_churn_renewables[amount_converted]),  
            ALLEXCEPT(  fac_cs_churn_renewables,
                        fac_cs_churn_renewables[close_date],
                        fac_cs_churn_renewables[account_id],
                        fac_cs_churn_renewables[account_segment]
            ),
            fac_cs_churn_renewables[close_date] > mindate && fac_cs_churn_renewables[close_date] <= maxdate
        )
    )
Var newcol = 
ADDCOLUMNS(
    mytable,"final_amount",
    IF([Prev Period] >= [Curr Period] , [Curr Period] , [Prev Period])
)


RETURN
newcol

So if I run this code to create a table then it works, however, when I try to change the returning value to sum the "final amount"... example below

RETURN
SUM(test[Prev Period])

and plug it into a measure, instead of a new table, the code doesn't work properly.

Any help is highly apriciated, thanks in advance!!!

[Ashish_Mathur]

Hi,

Share some data (in a format the can be pasted in an MS Excel file) and show the expected result.

[David_S]

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

[Ashish_Mathur]

Hi,

I believe Greg has already answered your question.

[Greg_Deckler]

@David_S It's hard to figure out what you need. It could be Measure Aggregation, a For Loop or a While Loop but not sure.

If it is a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

For Loop

While Loop

[David_S · Accepted solution]

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

[David_S]

Hi Greg,

Thanks for your responce, I actually tried that before but I am getting some weird results which I have tried to debug... but you know what the community thinks about SUMMARIZE, so I tried to go another way unsuccessful.