Get the list of User form the previous month not included in the range.

[Anonymous]

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.

[Greg_Deckler]

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

[Anonymous]

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;

[Greg_Deckler]

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],",")

[Anonymous]

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 )

[Anonymous · Accepted 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.