Forum Discussion
Rolling total customers
Hi all,
The goal is to see how many customers we have as a rolling total.
We have a sales table containing "Company ID" and "Order Date".
I would like to do a Rolling Total of Companies based on First Order Date.
I can do this easily by Power BI's "Quick Measure" for Rolling Total, however, I need a modification of this.
If a customer has not ordered for 18 months, the customer should not count anymore (but still count before that).
Example:
If customer A had a first order Jan2010, and a last order Dec 2012, the customer should be included in the Rolling Total from Jan2010 (First order) and until June2014 (18 months after the last order of Dec2012), and not after that.
Can you help me do that?
Best,
6 Replies
- amitchandak
Super User
Anonymous , with a table this how you can 18 months of data month by month. You can distinct count customer to get an active count
example
Rolling 18 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-18,MONTH)) Rolling 18 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-18,MONTH))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.- amitchandak
Anonymous , you may need, it depends
Rolling 18 = CALCULATE(distinctcount(Sales[Customer ID]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-18,MONTH)) Rolling 18 = CALCULATE(distinctcount(Sales[Customer ID]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-18,MONTH))
