Hello,
I was looking for help expanding my current months formula to be more dynamic. The problem with my existing logic is it will not Show past months without updating hardcoding -1, -2 and so on. If a user clicks on February vs January I want to show the new customers in the selected Month.
I have successfully applied the concept to the current month.
Customer with the last invoice date less than start of this month. This gives me new customers by applying isNull in my If.
=IF(
IsNull(DATE(AGGR(MAX({1<InvoiceDate = {'<$(=monthstart(AddMonths(Date(Today()),0)))'}>} InvoiceDate),CustomerKey) ))
AND DATE(AGGR(MIN({1<ActivityMonthYear = {'$(=date(monthstart(AddMonths(Date(Today()),0)), 'MMM-YY'))'}>} InvoiceDate),CustomerKey) ) >= monthstart(AddMonths(Date(Today()),0))
,InvoiceMonthYear
)
| Current Month Date | Current Month | |
| Customer | DATE(AGGR(MAX({1<InvoiceDate = {'<$(=monthstart(AddMonths(Date(Today()),0)))'}>} InvoiceDate),CustomerKey) ) | AND DATE(AGGR(MIN({1<ActivityMonthYear = {'$(=date(monthstart(AddMonths(Date(Today()),0)), 'MMM-YY'))'}>} InvoiceDate),CustomerKey) | monthstart(AddMonths(Date(Today()),0)) |
| 1 | - | 3/13/2015 | 3/1/2015 |
| 2 | - | 3/13/2015 | 3/1/2015 |
| 3 | - | 3/13/2015 | 3/1/2015 |
| 4 | - | 3/12/2015 | 3/1/2015 |
| 5 | - | 3/11/2015 | 3/1/2015 |
| 6 | - | 3/11/2015 | 3/1/2015 |
| 7 | - | 3/10/2015 | 3/1/2015 |
| 8 | - | 3/6/2015 | 3/1/2015 |
| 9 | - | 3/6/2015 | 3/1/2015 |
| 10 | - | 3/6/2015 | 3/1/2015 |
| 11 | - | 3/2/2015 | 3/1/2015 |
| 12 | 2/27/2015 | 2/27/2015 | 3/1/2015 |
| 13 | 2/27/2015 | 2/27/2015 | 3/1/2015 |
