Rolling total customers

[PeterStuhr]

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,

[amitchandak]

@PeterStuhr , 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-Y...


Appreciate your Kudos.

[PeterStuhr]

Hi @amitchandak 

Sorry I don't follow exactly. Can you show me the distinctcount calculation?

[amitchandak]

@PeterStuhr , 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))  

[PeterStuhr]

Hi, it works cool!

Thanks.

However, would it be possible for me to see in the graph which months a selected "company" is included?

Now, If I filter by a specific company, it shows in the graph the months it had "sales":

[v-alq-msft]

Hi, @PeterStuhr 

I'd like to suggest you use Edit interaction to modify the interaction type as 'Filter'.

Best Regards

Allan

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

[PeterStuhr]

Hi @v-alq-msft 

Thanks, I know I can change that. My problem is a bit different, let me try to explain.

First, I want to count how many companies at a given month, have had orders the last 18 months. That works.

However, if I want to see what companies are "included" for a given month, it doesnt quite work.

Example:

Lets say Company A has had an order Jan 2019, and nothing else. Then that company should be included from Jan2019-June2020 (18 months).

So if I for instance clicked on July 2019, I would like to see that "Company A" is included in that month. But how it works now is, if I click on July 2019, it only shows the companies that actually had an order that month (Which Company A has not, but should still count). Does it make sense? I have a relationship between my Date Table and Sales Date column. And on my Axis I am using "Date", so I was wondering if it is because when I click July 2019, the date filters the Sales Date, why the Company A is not shown (even though is should show because of my -18 months measure).

Can I fix that?