Time Intelligence Invoice Date & Invoice Pay Date

[cdawidow]

Hi guys, just running into a brick wall here.  My A/R team groups invoices by the month they were eventually paid and not by days.  So for example.  An invoice that was Invoiced in February 17th 2020 and was paid on March 22 2020 would be grouped in AGE 1. Following the same example, if the invoice was paid on April it would be grouped in AGE 2. 

My question is how to use DAX to follow this logic so I get the right invoice amounts for these aged groups?

I tried the following but its not coming out right.  Date is my date table.  I have in

cluded the relationship set up below between the date table and A/R table.  Any help would be appreciated. 

AGE 2 = VAR X = FIRSTDATE('DATE'[Date])

VAR Y = CALCULATE(LASTDATE('DATE'[Date]),DATEADD('DATE'[Date],2,MONTH)) RETURN CALCULATE(SUM('A/R'[INVAM]),FILTER('A/R','A/R'[INVOICE DATE]>=X && 'A/R'[INVOICE PAY DATE]<=Y))

[Anonymous]

You've got 2 date fields in the main table so you have to either disconnect your Date table from it or have 2 relationships between the tables out of which one will be inactive and activated with USERELATIONSHIP within a measure. Or, you could have 2 different Date tables, one for invoice date and the other one for invoice payment day. What you choose depends on what you want to calculate in your model. If you want to be able to show on one time axis the number of invoices raised and paid, then you should opt for one Date table with either no connection to the main table or 2 connections (one inactive).

[amitchandak]

@cdawidow , ate you trying to look for an age bucket?

if they are from same table we can create

New column =

var _age = datediff([invoice Date], [paid date], day)

return 

Switch(True() ,

_age <20, "  Below 20" ,

_age <30 , " Between 20-30",

//Add others

)

But if these are not from the same table then you need to measure 

datediff(Min(Table[invoice Date]), Max(Table2[paid date]), day)

Now you need to do segmentation or binning using an independent table

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[cdawidow]

Hi Amit, I am and I understand your measure.  But my orginization focuses on the month the invoice is actually paid and not the days.  For example. if a invoice is Issued in Feb but payed sometime in March then that would be in AGE 1.  If that same invoice was paid in April, it would be Age 2 because it is paid in the month of April, 2 months after the issue month of February.