Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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:
Hope this helps.
Best Regards,
Giotto Zhi
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
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:
Hope this helps.
Best Regards,
Giotto Zhi
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!
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
Thank you very much @v-gizhi-msft ! Works PERFECTLY!
All the best from Croatia!
Thanks!
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.
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.
User | Count |
---|---|
101 | |
90 | |
83 | |
72 | |
66 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |