how to sum up records based on overdue days critria

[Jeanxyz]

Hi, 

I need to sum up the invoice amounts from my fact_invoice table, based on overdue days, the challenge is the overdue days calculation is dynamic based on user's date selection of Dim_SlicerDate. 

I have created a measure below to sum up invoice amounts with overdue days between 31-60 days. It works on invoice level (see printscreen below). However, it won't work if I use the measure on legal entitiy level. The issue is in each entity, there are mulple invoices, hence my var overdue_days calculation is not correct.

overdue_EUR_31_60_All_Remesure (M) =

var max_date=min(today(),max(Dim_SlicerDate[Date]))

var overdue_days=max_date - max(Fact_Invoice[Due date])

var amt1=

if(and(overdue_days<=60, overdue_days>30),

sumx(

values(Fact_Invoice[Comp Curr.]),

Divide(

calculate([overdue amount_comp curr_All(M)],Fact_Invoice[Comp Curr.]=earlier(Fact_Invoice[Comp Curr.])),

calculate(max('Dim_Exchange Rates'[Month End Rate]),filter('Dim_Exchange Rates',max(Dim_SlicerDate[YearMonth])='Dim_Exchange Rates'[YearMonth] && 'Dim_Exchange Rates'[Converted Currency]=earlier(Fact_Invoice[Comp Curr.]))))))

Var comp_amt_remeasure= calculate(sum('Fact_AR Remeasure'[Balance Company Currency]),Dim_Date[Date] <=max(Dim_SlicerDate[Date]),'Fact_AR Remeasure'[Posting Date Check]<>"exclude")

Var comp_amt_split=divide(comp_amt_remeasure,[open amount_comp curr_All(M)])

Var EUR_amt_all= amt1*(1+comp_amt_split)

Return

If(EUR_amt_all=0,blank(),format(EUR_amt_all,"#,0"))

[johnt75 · Accepted solution]

Perhaps a different approach. Write a measure, if you don't have one, which calculates the amount due at an invoice level, and includes all your logic about currency conversion. The basic concept would be SUMX('Invoice Table', 'Invoice Table'[Amount]).

Rather than trying to calculate the number of days overdue for each invoice, work out the start and end date of your 31-60 day window, and then use DATESBETWEEN. The basic concept would be

Overdue 31-60 days =
var endDate = SELECTEDVALUE('Date slicer'[Date])
var startDate = endDate - 30
RETURN CALCULATE( [Invoice Amount], DATESBETWEEN('Date'[Date], startDate, endDate ) )

[Jeanxyz]

Thank you so much! You inspired me, actually I can filter fact_invoice table using [Due Date] column. 

Here is the updated measure and it seems to work well. 

overdue_EUR_31_60_All_Remesure (M) =

var max_date=min(today(),max(Dim_SlicerDate[Date]))

var overdue_days=max_date - max(Fact_Invoice[Due date])

var amt1=

sumx(

values(Fact_Invoice[Comp Curr.]),

Divide(

calculate([overdue amount_comp curr_All(M)],Fact_Invoice[Comp Curr.]=earlier(Fact_Invoice[Comp Curr.]), Fact_Invoice[Due Date]<= max_date-31,Fact_Invoice[Due Date]> max_date-61 ),

calculate(max('Dim_Exchange Rates'[Month End Rate]),filter('Dim_Exchange Rates',max(Dim_SlicerDate[YearMonth])='Dim_Exchange Rates'[YearMonth] && 'Dim_Exchange Rates'[Converted Currency]=earlier(Fact_Invoice[Comp Curr.])))))

Var comp_amt_remeasure= calculate(sum('Fact_AR Remeasure'[Balance Company Currency]),Dim_Date[Date] <=max(Dim_SlicerDate[Date]),'Fact_AR Remeasure'[Posting Date Check]<>"exclude")

Var comp_amt_split=divide(comp_amt_remeasure,[open amount_comp curr_All(M)])

Var EUR_amt_all= amt1*(1+comp_amt_split)

Return

If(EUR_amt_all=0,blank(),format(EUR_amt_all,"#,0"))