Days wise calculation of Overdue Amount for selected month...

[ashrat001]

Hi Experts,

Hope you are good. Currently, I'm calculating Overdue amount by passing user selected date for analysis for 1-30 days Overdue, 31-60 days overdue, 91-120 days overdue, 121-360 days overdue and finally >360 days overdue. I calcuate these slabs for Open Invoice/Items and also for closed for historical data. And for Total Overdue I add up all these split/slabs.

Now my requirement is to calculate the Total Overdue split days wise for selected month. Below is my measure for 1-30 days calculation for Item Status = 'O'.

1-30 Days =

VAR LastDay= MAX(KeyDate[Date]) //should return last date in selected period

RETURN

SUMX(

FILTER(

'Table',

'Table'[Net due date.Net due date Level 01]<LastDay && 'Table'[Net due date.Net due date Level 01]>=LastDay-30 && 'Table'[Item Status.Item Status Level 01] = "O"

),

'Table'[Debit amt in LC]

)

Below is my measure for 1-30 days calculation for Item Status = 'C'.

 1-30 Days2 =

VAR LastDay= MAX(KeyDate[Date]) //should return last date in selected period

RETURN

SUMX(

FILTER(

'Table',

'Table'[Clearing.Clearing Level 01]>LastDay && 'Table'[Net due date.Net due date Level 01] < LastDay && 'Table'[Net due date.Net due date Level 01]>=LastDay-30 && 'Table'[Item Status.Item Status Level 01] = "C"

),

'Table'[Debit amt in LC]

)

How to approach this day wise caluation for a selected month efficiently?

I would appreciate your valuable inputs. 

Many thanks!

Tariq Ashraf

[amitchandak]

@ashrat001 . In such case you should use date table. In this case date table can be independent (not joined to table) or use crossfilter to remove

1-30 Days =
VAR LastDay= MAXX(allselected('Date'), 'Date'[Date]) //should return last date in selected period
RETURN
SUMX(
FILTER(
'Table',
'Table'[Net due date.Net due date Level 01]<LastDay && 'Table'[Net due date.Net due date Level 01]>=LastDay-30 && 'Table'[Item Status.Item Status Level 01] = "O"
),
'Table'[Debit amt in LC]
)

With cross filter if joined

1-30 Days =
VAR LastDay= MAXX(allselected('Date'), 'Date'[Date]) //should return last date in selected period
RETURN
calculate(
SUMX(
FILTER(
'Table',
'Table'[Net due date.Net due date Level 01]<LastDay && 'Table'[Net due date.Net due date Level 01]>=LastDay-30 && 'Table'[Item Status.Item Status Level 01] = "O"
),
'Table'[Debit amt in LC]
), crossfilter('Table'[Net due date.Net due date Level 01,'Date'[Date]) )

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.