Dynamic calculations based on the selected date

[Deelip]

Hi All, 

I cannot think of an approach for my requirement on finding the outstanding amounts/number of days due based on the user selection.

I have the following table and with the expected output. Lets say if a user selects October 22nd 2024, I would like to have the total AR outstanding amount for the invoices where [closed at Date] is blank and also [closed at date] is greater than the user selected date. 

In the scenario below if we select a date as oct 22nd 2024; I am getting total amount based on rows where the closed at date is blank and closed at date is greater than oct 22nd 2024. 

 [not due] is something where my due date greater than date selected on the filter.

 [1-30] days is calculated based on the number of days between the [due date] to selected date on the filter.  

If you have any thought process on how to approach this kind of problem statement please share. Thanks!

[ryan_mayu · Accepted solution]

@Deelip 

you can try this

Total AR =

VAR _date=max('date'[Date])

return sumx(FILTER(all('Table'),'Table'[Customer]=max('Table'[Customer])&&('Table'[Closed at Date]>_date||ISBLANK('Table'[Closed at Date]))),'Table'[Amount])

not due =

VAR _date=max('date'[Date])

return sumx(FILTER(all('Table'),'Table'[Customer]=max('Table'[Customer])&&'Table'[Due Date]>_date),'Table'[Amount])

1-30days =

VAR _date=max('date'[Date])

VAR _tbl=ADDCOLUMNS('Table',"days",'Table'[Closed at Date]-_date)

return sumx(FILTER(_tbl,'Table'[Customer]=max('Table'[Customer])&&[days]>0 &&[days]<=30),'Table'[Amount])

 

pls see the attachment below

[Deelip]

@ryan_mayu 

Thank you so much. It worked as expected. Appreciate your help.

[ryan_mayu]

you are welcome