Calculating Gross Retention amount as a fixed value of another column

[tomislav_mi]

Hey guys,

Exploring DAX language and I am amazed so far. Most of the things I was able to get except for gross retention. I hope some of you know to get it. Need to get gross retention filtered by report dates in the pivot table.

These columns are my data set.

Index

Account Name

Customer

Customer Cohort

Report Date

Ending MRR

 I was able to get the net retained amount of customers who have signed before 12 months or more, but when I tried to do the same with the gross amount (if ending MRR before 12 months < ending MRR today THEN ending MRR today OTHERWISE ending MRR before 12 months) it worked good when I use "Customer" as rows. When I use "Report Dates" then the formula is applying on the sum of report dates.

I would need to have a fixed value of the sum of the customer gross retained amount but in a report date row.

Row Labels	Sum of Ending MRR (current)	Same Period Last Year MRR	12 months net retained MRR	GROSS RETAINED
4/30/2014	4569	2968.3097	3880.4182

Is it possible to get it?

I would be so grateful ie of you can help.

Many thanks!

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i think its a measure total issue.

So i extract some sample data from your Excel to test:

Then create a measure as your logic:

Measure 1 = MIN(SUM('Table'[Same period last year]),SUM('Table'[Sum of Ending MRR]))

It shows the same issue as you posted:

Then try this measure:

Measure 2 = SUMX(GROUPBY('Table','Table'[Date],'Table'[Customer ID]),CALCULATE(MIN(SUM('Table'[Same period last year]),SUM('Table'[Sum of Ending MRR]))))

The result shows:

Try to create a calculated column to check:

Gross retained = MIN('Table'[Same period last year],'Table'[Sum of Ending MRR])

It shows the same with measure 2:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i think its a measure total issue.

So i extract some sample data from your Excel to test:

Then create a measure as your logic:

Measure 1 = MIN(SUM('Table'[Same period last year]),SUM('Table'[Sum of Ending MRR]))

It shows the same issue as you posted:

Then try this measure:

Measure 2 = SUMX(GROUPBY('Table','Table'[Date],'Table'[Customer ID]),CALCULATE(MIN(SUM('Table'[Same period last year]),SUM('Table'[Sum of Ending MRR]))))

The result shows:

Try to create a calculated column to check:

Gross retained = MIN('Table'[Same period last year],'Table'[Sum of Ending MRR])

It shows the same with measure 2:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[tomislav_mi]

Hey, @v-gizhi-msft thank you very much for your response!!

Your solution is great! but there is only one small difference so I can't apply it - Same period last year is a measure in my model, but not calculated column - but you gave me a COOL idea, why shouldn't I created one column like this in big data set. But not sure how.

Can you please look at my data set: 

How could I get the desired Same period last year column (orange)? I tried with EARLIER and SAMEPERIODLASTYEAR but always stoped somewhere in the process.

Account Name	Ending MRR	Report Date	Same period last year MRR
A	50	7/31/2017
A	50	8/31/2017
A	58	9/30/2017
A	70	10/31/2017
A	50	11/30/2017
A	50	12/31/2017
A	50	1/31/2018
A	50	2/28/2018
A	50	3/31/2018
A	50	4/30/2018
A	50	5/31/2018
A	100	6/30/2018
A	100	7/31/2018	50
A	200	8/31/2018	50
A	200	9/30/2018	58
A	200	10/31/2018	70
B	200	1/31/2019
B	269	2/28/2019
B	200	3/31/2019
B	200	4/30/2019
B	200	5/31/2019
B	200	6/30/2019
B	200	7/31/2019
B	200	8/31/2019
B	200	9/30/2019
B	200	10/31/2019
B	50	11/30/2019
B	50	12/31/2019
B	50	1/31/2020	200
B	50	2/29/2020	269

Many thanks once more!

[v-gizhi-msft · Accepted solution]

Hi,

Please try this calculated column:

Same period last year MRR = 
VAR MinYearPerName =
    YEAR (
        CALCULATE (
            MIN ( 'Table'[Report Date] ),
            FILTER ( 'Table', 'Table'[Account Name] = EARLIER ( 'Table'[Account Name] ) )
        )
    )
VAR a = 'Table'[Report Date]
RETURN
    IF (
        YEAR ( 'Table'[Report Date] ) <> MinYearPerName,
        CALCULATE (
            MAX ( 'Table'[Ending MRR] ),
            FILTER (
                'Table',
                'Table'[Account Name] = EARLIER ( 'Table'[Account Name] )
                    && 'Table'[Report Date]
                        = DATE ( YEAR ( a ) - 1, MONTH ( a ) + 1, 1 ) - 1
            )
        ),
        BLANK ()
    )

The result shows:

Best Regards,

Giotto Zhi

[tomislav_mi]

Thank you very much @v-gizhi-msft ! Works PERFECTLY!

All the best from Croatia!

Thanks!

[Greg_Deckler]

It's a bit difficult to follow. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But, many times if you just want a fixed value for something you end up using ALL to ensure that you are not applying any filters. Or ALLEXCEPT if you want only some filters.

[tomislav_mi]

Hey @Greg_Deckler !

Thank you for the great post.

I am attaching the sample data that uses the real formulas. Attaching it in Excel Data model with DAX formulas becuase it is easier to visualize. If you can take a look I would be grateful.

https://mobilnainformatika-my.sharepoint.com/:x:/g/personal/tkozul_mobilnainformatika_onmicrosoft_co...

The explanation that I wrote before will have much more sense now.