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.
Hi all,
Quick one and I can't get my head around it. So I've got this data with the customer ID/number quantity sold and date sold and is renewed on a monthly basis unless cancelled.
I'm trying to calculate customers who may have lapsed in their renewal but then returned on a given month. So for example below is the sample data.
Again the same data in a matrix table where you can see customer 11 lapsed renewals in April and May but returned in June.
So that when I put the DAX in a matrix table it will show the returned quantity number in June of 2.
Solved! Go to Solution.
Hi, @Mike282
You can try to create calculate column as below:
Last renewal date =
MAXX (
TOPN (
1,
FILTER (
Sheet1,
Sheet1[Customer No] = EARLIER ( Sheet1[Customer No] )
&& Sheet1[Renewal date] < EARLIER ( Sheet1[Renewal date] )
),
Sheet1[Renewal date], DESC
),
Sheet1[Renewal date]
)
datediff = DATEDIFF('Sheet1'[Last renewal date],'Sheet1'[Renewal date],MONTH)
then create a new matrix and apply the filter pane
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Mike282 , Not very clear.
Can you share sample data and sample output in table format?
But if date diff is needed from the last date to check these are formulas that can help.
Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Diff Column= datediff(maxx(filter(Table,Table[Date]<earlier(Table[Date]) && Table[Numberf]= earlier(Table[Numberf]) ),Table[Date]) ,Table[Date],Day)
Hi @amitchandak
Apologies! Sample PBIX file attached. Will have a look at your solution in the meantime.
https://www.dropbox.com/s/hd5yeksng0dzj7d/Returning%20Customer%20Sample.pbix?dl=0
Hi, @Mike282
You can try to create calculate column as below:
Last renewal date =
MAXX (
TOPN (
1,
FILTER (
Sheet1,
Sheet1[Customer No] = EARLIER ( Sheet1[Customer No] )
&& Sheet1[Renewal date] < EARLIER ( Sheet1[Renewal date] )
),
Sheet1[Renewal date], DESC
),
Sheet1[Renewal date]
)
datediff = DATEDIFF('Sheet1'[Last renewal date],'Sheet1'[Renewal date],MONTH)
then create a new matrix and apply the filter pane
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |