Dynamic age brackets

[y_grynechko]

Hello,

I am trying to create a dynamic age brackets in the pivot table. Right now I have something like this in the load script:

if ([InvoiceDate] > Today(), 'Future Invoice',
if (isnull([InvoiceDate]), 'Date Missing',
If ([InvoiceDate] <= Today(),
If (Today() - InvoiceDate > -1,
If (Today() - InvoiceDate > 90, '91+ Days',
If (Today() - InvoiceDate > 60, '61 - 90 Days',
If (Today() - InvoiceDate > 30,'31 - 60 Days', '0 - 30 Days')))))))
as [Invoice Age Bracket]

But there is a need for it to be point in time. When Month is not selected this is fine but when user selects month it needs to be recalculated with the last day of month instead of Today(). 

Something like this, but working, for every age bracket: 

0-30

if(GetSelectedCount(Month) = 0,
Sum({< [Invoice ID] = {"=Today()- Date([Invoice Date])<=30"},>} AmtUSD),
Sum({< [Invoice ID] = {"=Max(Date([Invoice Date]))- Date([Invoice Date])<=30"}>} AmtUSD))

0-60

if(GetSelectedCount(Month) = 0,
Sum({< [Invoice ID] = {"=Today()- Date([Invoice Date])>30<=60"},>} AmtUSD),
Sum({< [Invoice ID] = {"=Max(Date([Invoice Date]))- Date([Invoice Date])>30<=60"}>} AmtUSD))

etc. 

[marcus_sommer]

I think I would use a calculated dimension like:

aggr(
if ([InvoiceDate] > Today(), 'Future Invoice',
if (isnull([InvoiceDate]), 'Date Missing',
If ([InvoiceDate] <= Today(),
If (Today() - InvoiceDate > -1,
If (Today() - InvoiceDate > 90, '91+ Days',
If (Today() - InvoiceDate > 60, '61 - 90 Days',
If (Today() - InvoiceDate > 30,'31 - 60 Days', '0 - 30 Days'))))))), InvoiceID)

and your Today() might be replaced with something like:

rangemin(Today(), monthend(max(Date)))

or some more complex logic if the date could be also in the future or if you want to include some further conditions.

- Marcus

[y_grynechko]

Hey Marcus,

thank you for the reply. The issue with this solution is that every invoice date is going to be compared to its own monthend:

 After selecting January you can see that age brackets for November invoices is incorrect(well, it is correct but it is being calculated from the end of November and I need it to be calculated from the end of January).

[marcus_sommer]

In this case I'm not sure if it's really suitable to use InvoiceDate as base for your calendar-fields respectively selections and maybe an independent calendar could be more practically. Nevertheless you could bypass it by replacing:

InvoiceDate

with:

only({< InvoiceDate = >} InvoiceDate )

within the aggr-dimension.

- Marcus