Forum Discussion
Resolver IDynamic calculations based on the selected date
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!
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
3 Replies
ryan_mayuSuper User
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
