Forum Discussion
Accounts Payable past and current
I am trying to build a payables report.
I have cleaned the data to the best of my ability (brand new to power bi)
I need to be able to input a date and know the payables for that date, plus 30, 60, 90 days.
(this will be monthly)
I believe i am looking for a slicer for the date i want to research, and buckets for the 30, 60, 90.
I also unfortunatly have ALL of our invoices loaded, as I could not figure out how to filter our the paid invoices only if they were paid by the date entered ( i do have a paid date column). So once it is filtered by the due date i will need to filter out if it was paid.
changing the date needs to be easy.
in this sceenshot I add several columns i didnt include on my orginal, more for easy of showing whats available.
intr rec (9=paid 0=not)
rec date paid (paid date)
Already this report takes a very long time to load as it is pulling in ALL invoices.
Any help at all would be helpful.
Please note I am very very new to power bi
Hi Anonymous ,
I suggest you to create an unrelated date table and then create a measure to compare the select date and paid date.
DimDate(UnRelated) = CALENDARAUTO()Measure:
Dynamic INTR REC = VAR _SELECTDATE = SELECTEDVALUE('DimDate(UnRelated)'[Date]) RETURN IF(ISBLANK(MAX('Table'[REC DATE PAID])),0,IF(MAX('Table'[REC DATE PAID]) > _SELECTDATE,0,9))Result is as below. intr rec (9=paid 0=not) Here I select 2024/07/31, so if the SUPPLIER is paid before 2024/07/31, it will return 9, otherwise 0.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- Ashish_Mathur
Super User
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
- Ashish_Mathur
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
Hi Anonymous ,
I suggest you to create an unrelated date table and then create a measure to compare the select date and paid date.
DimDate(UnRelated) = CALENDARAUTO()Measure:
Dynamic INTR REC = VAR _SELECTDATE = SELECTEDVALUE('DimDate(UnRelated)'[Date]) RETURN IF(ISBLANK(MAX('Table'[REC DATE PAID])),0,IF(MAX('Table'[REC DATE PAID]) > _SELECTDATE,0,9))Result is as below. intr rec (9=paid 0=not) Here I select 2024/07/31, so if the SUPPLIER is paid before 2024/07/31, it will return 9, otherwise 0.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
