topic Re: Time Intelligence Invoice Date & Invoice Pay Date in DAX Commands and Tips

Time Intelligence Invoice Date & Invoice Pay Date

cdawidow — Thu, 27 May 2021 12:14:45 GMT

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))

Re: Time Intelligence Invoice Date & Invoice Pay Date

amitchandak — Thu, 27 May 2021 14:23:38 GMT

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

Re: Time Intelligence Invoice Date & Invoice Pay Date

Anonymous — Thu, 27 May 2021 14:30:47 GMT

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).

Re: Time Intelligence Invoice Date & Invoice Pay Date

cdawidow — Thu, 27 May 2021 14:37:47 GMT

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.