Datediff multiple rows / group of date periods / distinctcount of users

[patricia_csf]

Hi,

I have two tables (with proper relationship):

1) User ID + registration date

2) User ID + profit per day (several rows)

I would like to have measures to:

- See the difference between registration date and day of profit (problem I have is that I have multiple profit inputs per user ID)

- To be able to split the data as: 

Between 0 and 6 months after registration, "1.000" users (userID) generated a sum of "$10.000" profit

Between 6 and 12 months after registration, "800" users (userID) generated a sum of "$6.000" profit

Between 12 and 18 months after registration, "500" users (userID) generated a sum of "$8.000" profit

Can you help me on the measures?

Thx in advance. 🙂

[rcharara · Accepted solution]

Add a new column to your profit table

Delta Profit Months - DATEDIFF(RELATED(CustMaster[Registration Date]),Benefits[Benefit Day],MES)

Then add another column

Date Range: SWITCH(TRUE()

, Benefits[Delta Profit Months] <-6, "0..6 Months"

, Benefits[Delta Profit Months] > 6 && Benefits[Delta Profit Months] <-12 , "6..12 Months"

,

"More than 12 months")

Then, on the ribbon, choose Table Tools and New Table

Summary of Benefits: SUMMARY(Benefits,Benefits[Date Range], "No. COUNTROWS(Profits), "Sum of profit", SUM(Profits[Profit]))