Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
aka churn in my company.
Let say I have a transaction table with timestamp, amount... and userId.
I have a filter context for the time with a slicer and my goal is to count (later having the list of) the number of users who have made a payment in the previous month of the lower date range who isn't present in the list of actual user who paied (is that clear enough?)
I end up writting somethinng like (association == user):
Churn = VAR previousMinMonth = MONTH ( MIN ( 'Date'[Date] ) ) - 1 VAR listOfAssociationIdInRange = DISTINCT ( Paiements[id_association] ) VAR ListOfAssociationIdInTargetedMonth = CALCULATE ( DISTINCT ( ALL ( Paiements[id_association] ) ), FILTER ( Paiements, Paiements[date] >= previousMinMonth && Paiements[date] < MIN ( 'Date'[Date] ) ) ) RETURN COUNTROWS ( ListOfAssociationIdInTargetedMonth )
I think I'm not that far with this but it's currently not working.
Solved! Go to Solution.
I finally could have the measure I wanted with :
Churn = VAR previousMinMonth = PREVIOUSMONTH ( 'Date'[Date] ) VAR listOfAssociationIdInRange = DISTINCT( Paiements[id_association]) VAR ListOfAssociationIdInTargetedMonth = CALCULATETABLE( DISTINCT( Paiements[id_association] ), FILTER ( ALL ( Paiements ), Paiements[date] >= STARTOFMONTH ( previousMinMonth ) && Paiements[date] <= ENDOFMONTH( previousMinMonth ) ) ) VAR excludedTable = EXCEPT ( ListOfAssociationIdInTargetedMonth, listOfAssociationIdInRange ) RETURN COUNTROWS(excludedTable)
Thanks @Greg_Deckler for the hints.
Nope, not really all that clear. Sample data and expected output would be very helpful. 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.
You might check out Not In Common: https://community.powerbi.com/t5/Quick-Measures-Gallery/Not-In-Common/m-p/388273
Here a sample of data.
I'm targeting "date" as a date of payment and it's the distinct list of "id_association" that I try to get.
The simplified SQL equivalent of what i'm trying to do is :
SELECT DISTINCT a.id, a.nom, aa.[state] FROM dbo.paiement p JOIN dbo.association a ON a.id = p.id_association JOIN dbo.association_application aa ON aa.id_association = a.id WHERE p.statut = 'AUTHORIZED' AND CONVERT(DATE, p.date) BETWEEN '2018-01-01' AND '2018-01-31' AND NOT EXISTS ( SELECT 1 FROM dbo.paiement p2 WHERE p2.id_association = p.id_association AND p2.statut = 'AUTHORIZED' AND CONVERT(DATE, p2.date) BETWEEN '2018-02-01' AND '2019-01-31' ) ORDER BY 3;
OK, I believe a variation of Not In Common is what you are looking for. This makes a few assumptions but something along the lines of:
NotInCommon = VAR __month = MONTH(TODAY()) VAR __year = YEAR(TODAY()) VAR __tableA = CALCULATETABLE(DISTINCT(Table[id_association]),FILTER(ALL('Table'),MONTH(MAX([Date])) = __month && YEAR(MAX([Date])) = __year)) VAR __tableB = CALCULATETABLE(DISTINCT(Table[id_association]),FILTER(ALL('Table'),MONTH(MAX([Date])) = __month - 1 && YEAR(MAX([Date])) = __year)) VAR __results = EXCEPT(__tableA,__tableB) RETURN CONCATENATEX(__results,[id_association],",")
That's helping me a lot thanks.
I explore and discover more and more new functions everyday with DAX and I find myself think of new possibilities.
Now I have this version of the measure, but I still have an issue in my variable `ListOfAssociationIdInTargetedMonth` :
A table of multiple values was supplied where a single value was expected.
I think it's my All(Paiements) that get triggered but I cannot see how do I have to modify this.
Churn = VAR previousMinMonth = PREVIOUSMONTH ( 'Date'[Date] ) VAR listOfAssociationIdInRange = DISTINCT ( Paiements[id_association] ) VAR ListOfAssociationIdInTargetedMonth = CALCULATETABLE ( DISTINCT ( Paiements[id_association] ), FILTER ( ALL ( Paiements ), DATESBETWEEN ( 'Date'[Date], STARTOFMONTH ( previousMinMonth ), ENDOFMONTH ( previousMinMonth ) ) ) ) VAR excludedTable = EXCEPT ( ListOfAssociationIdInTargetedMonth, listOfAssociationIdInRange ) RETURN COUNTROWS ( ListOfAssociationIdInTargetedMonth )
I finally could have the measure I wanted with :
Churn = VAR previousMinMonth = PREVIOUSMONTH ( 'Date'[Date] ) VAR listOfAssociationIdInRange = DISTINCT( Paiements[id_association]) VAR ListOfAssociationIdInTargetedMonth = CALCULATETABLE( DISTINCT( Paiements[id_association] ), FILTER ( ALL ( Paiements ), Paiements[date] >= STARTOFMONTH ( previousMinMonth ) && Paiements[date] <= ENDOFMONTH( previousMinMonth ) ) ) VAR excludedTable = EXCEPT ( ListOfAssociationIdInTargetedMonth, listOfAssociationIdInRange ) RETURN COUNTROWS(excludedTable)
Thanks @Greg_Deckler for the hints.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |