Sum Sales based on Special Date (Exclude after 18 months)

[PeterStuhr]

Hi all,

I have a request I would like some help with.

We have different products: "A", "B" and "C". In our Sales Table, we have columns like "Company ID", "Product", "Sales Date", "Revenue".

I have a relation to our DimCompanies table (based on CompanyID) that sets the "Latest Product A Order Date", as that is important to us to know.

We want to do a report for Customers that has - at some point - ordered our "A" product. We want to see their FULL spent for all products, from the date they ordered "A" and 18 months ahead. If they do not buy our "A" product for 18 months, we don't want to include them in the report anymore.

So basically, on each Company we have a "Latest Product A Order Date" (if any). If they have a date, we want to include them in our reporting for the next 18 months (if they keep buying "A" they stay forever in the report).

Example: 

"Company 1" Orders our "A" product Jan2000-> Until today. We want to see their monthly spent for all months.

"Company 2" Orders our "A" product Jan2000 and never again. We want to see their monthly spent for Jan2000-June2001. After that, exclude from report.

"Company 3" Orders our "A" product Jan2000-->Dec2010. We want to see their monthly spent for Jan2000 (First "A" order Date) -> June2012 (18 Months after last "A" order Date).

Can someone help me with this?

Thanks!

In this example, product A is important for us.

[PeterStuhr · Accepted solution]

I managed to fix!

Step 1:  Compare "Order Date" to "Last A Product Date":

DateDiff Order vs Last A Date = DATEDIFF(FullSales[Order Start Date.Date], FullSales[Last Connector Date], MONTH)

Step 2: Make Exclude / Include measure:

Include_Exclude = IF(FullSales[DateDiff Order vs Last A Date] <-18,"Exclude","Include")

Filter for "Include".

[v-kelly-msft]

Hi  @PeterStuhr ,

Well done! Could you pls mark the reply as answered to close it?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.