Forum Discussion
Accrual Report Based on date
Hello,
We have to report on days worked in current month but not invoiced (Accruals).
Currently we export into excel and do it manually and ive been trying to do it in powerbi with no luck.
Below is an example of what we are looking for. The system gives us the first 6 columns and id like powerbi to calculate the rest.
I have powerbi giving me a month end/start from a dropdown slicer but the other fields just wont cooperate.
| Item | Start | End | Days Calc | Price | Total | Month end | Month Start | Accrued start | Accrued end | Accrued Days |
| Item 1 | 9/15/2023 | 11/1/2023 | 47 | 100 | 4700 | 10/31/2023 | 10/1/2023 | 10/1/2023 | 10/31/2023 | 30 |
| Item 2 | 9/15/2023 | 11/1/2023 | 47 | 150 | 7050 | 10/31/2023 | 10/1/2023 | 10/1/2023 | 10/31/2023 | 30 |
| Item 3 | 10/15/2023 | 10/20/2023 | 5 | 10 | 50 | 10/31/2023 | 10/1/2023 | 10/15/2023 | 10/20/2023 | 5 |
| Item 4 | 10/15/2023 | 10/20/2023 | 5 | 30 | 150 | 10/31/2023 | 10/1/2023 | 10/15/2023 | 10/20/2023 | 5 |
| Item 5 | 10/16/2023 | 10/21/2023 | 5 | 40 | 200 | 10/31/2023 | 10/1/2023 | 10/16/2023 | 10/21/2023 | 5 |
| Item 6 | 10/17/2023 | 10/22/2023 | 5 | 60 | 300 | 10/31/2023 | 10/1/2023 | 10/17/2023 | 10/22/2023 | 5 |
| Item 7 | 10/17/2023 | 10/22/2023 | 5 | 100 | 500 | 10/31/2023 | 10/1/2023 | 10/17/2023 | 10/22/2023 | 5 |
| Item 8 | 10/17/2023 | 11/22/2023 | 36 | 200 | 7200 | 10/31/2023 | 10/1/2023 | 10/17/2023 | 10/31/2023 | 14 |
| Item 9 | 10/17/2023 | 11/22/2023 | 36 | 10 | 360 | 10/31/2023 | 10/1/2023 | 10/17/2023 | 10/31/2023 | 14 |
| Item 10 | 10/17/2023 | 11/22/2023 | 36 | 30 | 1080 | 10/31/2023 | 10/1/2023 | 10/17/2023 | 10/31/2023 | 14 |
| Item 11 | 10/17/2023 | 11/22/2023 | 36 | 50 | 1800 | 10/31/2023 | 10/1/2023 | 10/17/2023 | 10/31/2023 | 14 |
| Item 12 | 10/17/2023 | 11/22/2023 | 36 | 90 | 3240 | 10/31/2023 | 10/1/2023 | 10/17/2023 | 10/31/2023 | 14 |
| Item 13 | 11/1/2023 | 11/20/2023 | 19 | 300 | 5700 | 10/31/2023 | 10/1/2023 | 11/1/2023 | 10/31/2023 | -1 |
| Item 14 | 11/1/2023 | 11/20/2023 | 19 | 50 | 950 | 10/31/2023 | 10/1/2023 | 11/1/2023 | 10/31/2023 | -1 |
| Item 15 | 11/1/2023 | 11/20/2023 | 19 | 10 | 190 | 10/31/2023 | 10/1/2023 | 11/1/2023 | 10/31/2023 | -1 |
Data is in dataverse, id perfer direct query but can live without.
2 Replies
I appologize for the horrible looking table ive tried everything to style it...the fourm just keeps removeing the style 😞
- amitchandak
Super User
TMorrice , Try like
month end =maxx(allselected(Date), Date[Date])
Month Start = Minx(allselected(Date), Date[Date])
Acrual End =
var _max = maxx(allselected(Date), Date[Date])
return
if(Max(Table[End]) > _max , _max, Max(Table[End]) )
Acrual Start =
var _max = Minx(allselected(Date), Date[Date])
return
if(Max(Table[Start]) < _min , _min, Max(Table[Start]) )Accrued Days = datediff([Acrual Start], [Acrual End] , day)
Add +1 to Accrued Days, if needed
