I need help with writing a DAX measure for a kind of "cohort" analysis

[Landon1]

Hello,

first I'm new here, I didn't find an opportunity to attach a pbix file, how is this possible?

My problem is the following: I have a data table, a simplified version looks like this.

Now I would like to have two filters in Power BI, I thought about two disconnected tables. They should allow to set a "Start Date" and an "End Date". Then I want to see, how the Customers have developed between Start and End date. The tricky part is, that I only want to compare the customers, that are available in the selected Start Date. The next screenshot shows a wrong result, but basically this is how I want to display it in Power BI:

The forumla for "End" is currently:

End =
VAR EndDate = MIN('End Date'[Date])
Return CALCULATE(DISTINCTCOUNT(Data[Customer]),FILTER(Data,Data[Date]=EndDate))

Now I would like to have a formula, which takes only the 2 customers selected for the Start (this is customer nr 1 and 2), and shows what the current Priority for these two customers is at the "End" Date. The right result would be (as customer 2 was Priority 4 in Jan 2000 and moved to Priority 1 in Jun 2000):

Would be really great to get some help here, I am already despairing and trying to solve the problem for a long time

[v-rzhou-msft · Accepted solution]

Hi @Landon1 ,

I suggest you to try this code to create a measure.

End =
VAR StartDate =
    MIN ( 'Start Date'[Date] )
VAR EndDate =
    MIN ( 'End Date'[Date] )
VAR LIST =
    CALCULATETABLE (
        VALUES ( Data[Customer] ),
        FILTER ( ALL ( Data ), Data[Date] = StartDate )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Data[Customer] ),
        FILTER ( Data, Data[Date] = EndDate && Data[Customer] IN LIST )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.