Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Everyone,
I've been struggling with this for awhile, and I cant seem to figure out how to solve this in DAX. I need help!
A table below is my dataset that contains data from 2 extration date.
What I'm trying to achieve is below:
As of Oct/31/2022(data extration date), I have a total of 11 memebership ID, 2 of them(ID: 5 and 6) that their membership exprires on Oct/31/2022,
When I pulled the data a month later on Nov/30/2022 (data extration date) and append with the date from prevous extration date, since ID 1, 2, 3, 4 did renew their membership, only ID 5, and 6 have been renewed their membership with a new "membership end date" as Nov/30/2023
Membership ID | Membership End date | Data extraction date |
1 | Oct/31/2022 | Oct/31/2022 |
2 | Oct/31/2022 | Oct/31/2022 |
3 | Oct/31/2022 | Oct/31/2022 |
4 | Oct/31/2022 | Oct/31/2022 |
5 | Oct/31/2022 | Oct/31/2022 |
6 | Oct/31/2022 | Oct/31/2022 |
7 | Dec/31/2022 | Oct/31/2022 |
8 | Jan/31/2023 | Oct/31/2022 |
9 | Mar/31/2023 | Oct/31/2022 |
10 | Feb/28/2023 | Oct/31/2022 |
11 | Apr/30/2023 | Oct/31/2022 |
5 | Nov/30/2023 | Nov/30/2022 |
6 | Nov/30/2023 | Nov/30/2022 |
7 | Dec/31/2022 | Nov/30/2022 |
8 | Jan/31/2023 | Nov/30/2022 |
9 | Mar/31/2023 | Nov/30/2022 |
10 | Feb/28/2023 | Nov/30/2022 |
11 | Apr/30/2023 | Nov/30/2022 |
is there a way (measure or other ways) to achieve:
calulating the number of ID that did not renew in the latest data extration date?
in the example above, it would be something like: 4(ID 1,2,3,4) out 6(ID: 1,2,3,4,5,6) that did not renew,
when the membership end date= data extration date.
In other words, I have to write some dax to lookup that ID 1, 2, 3, 4,5,6 from the previous data extration date(oct/31/2022) to see if they show up in the new date extration date (nov/30/2022), only ID 5 and 6 appears in the new data extration (nov/30/2022) because their membership has renewed(now with new membership end date as nov/30/2023 comparing to oct/31/2022 in the previous data extration date)
see below screenshot for done in excel manually with vlookup.
Thanks in advance for your help!
Solved! Go to Solution.
@toxicshev you can use something like this
Measure:=
VAR immediatelyPrecedingExtractionDate =
CALCULATE (
[extractionDate],
ALL ( 'fact' ),
OFFSET (
-1,
ALL ( 'fact'[Data extraction date] ),
ORDERBY ( 'fact'[Data extraction date], ASC )
)
)
VAR _left =
SUMMARIZE (
FILTER (
ALL ( 'fact' ),
'fact'[Data extraction date] = immediatelyPrecedingExtractionDate
),
'fact'[Membership ID]
)
VAR __right =
VALUES ( 'fact'[Membership ID] )
VAR _anti =
EXCEPT ( _left, __right )
RETURN
COUNTX ( _anti, [Membership ID] )
@toxicshev you can use something like this
Measure:=
VAR immediatelyPrecedingExtractionDate =
CALCULATE (
[extractionDate],
ALL ( 'fact' ),
OFFSET (
-1,
ALL ( 'fact'[Data extraction date] ),
ORDERBY ( 'fact'[Data extraction date], ASC )
)
)
VAR _left =
SUMMARIZE (
FILTER (
ALL ( 'fact' ),
'fact'[Data extraction date] = immediatelyPrecedingExtractionDate
),
'fact'[Membership ID]
)
VAR __right =
VALUES ( 'fact'[Membership ID] )
VAR _anti =
EXCEPT ( _left, __right )
RETURN
COUNTX ( _anti, [Membership ID] )
Thanks a lot, much appreciated your help!
Tested in Power BI, it worked perfectly.
Any chance you know if I can replace OFFSET with an alternative in Power Pivot?
Thanks again for your help!
@toxicshev , You should create measures based on Data extraction date having Membership End date>= Data extraction date
then we can create a current/prior measures (monthly( and check retained
example
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Thanks, but still cant figure it out. Do you mind to write a dax?
Can someone help please? Much appreciated!
User | Count |
---|---|
75 | |
74 | |
44 | |
31 | |
27 |
User | Count |
---|---|
99 | |
89 | |
52 | |
48 | |
46 |