Forum Discussion
Mous007
Helper IV
Helper IVSum AP due before EOM ?
Hi all,
So i have the below measure calculated and its working correctly on my report but i want to use it to calculate three new measures that would calculate:
- only the invoices to be paid before the end of the month (last day of current month). i tried to use a function called EOMONTH but could not get it to work.
- second measure to only calculate the sum of invoices Where the due date is < first day of next month.
- measure to only calculate the sum of invoices Where the due date is > last day of current month.
I also know that the first two might give the same result but i would need to calculate them if possible.
I have a normal date table and i can also post the query i used to generate the table if needed.
My measure:
Total AP (Other than validated) =
VAR _ConversionRate = SELECTEDVALUE('Currency table disconnected'[Currency Value],1)
Return
CALCULATE(SUMX('Payables',DIVIDE( Payables'[Invoice Amount] *
RELATED('Currency table' [Currency value]),_ConversionRate)),
'Payables'[Invoice Status] <> "Validated")
Data extract:
| Invoice Num | Invoice Amount | Due date |
| Invoice 1 | 254.57 | 4/29/2020 |
| Invoice 2 | 608.08 | 4/30/2020 |
| Invoice 3 | 6114.84 | 5/1/2020 |
| Invoice 4 | 898.03 | 5/2/2020 |
| Invoice 5 | 160 | 5/3/2020 |
| Invoice 6 | 3953.4 | 5/4/2020 |
| Invoice 7 | 4135.42 | 5/5/2020 |
| Invoice 8 | 11477.8 | 5/6/2020 |
| Invoice 9 | 942.5 | 5/7/2020 |
| Invoice 10 | 3605.47 | 5/8/2020 |
| Invoice 11 | 18685.84 | 5/9/2020 |
| Invoice 12 | 6988.8 | 5/10/2020 |
| Invoice 13 | 7474.34 | 5/11/2020 |
| Invoice 14 | 32732.2 | 5/12/2020 |
| Invoice 15 | 18919.69 | 5/13/2020 |
| Invoice 16 | 964.51 | 5/14/2020 |
| Invoice 17 | 6872.54 | 5/15/2020 |
| Invoice 18 | 126.86 | 5/16/2020 |
| Invoice 19 | 27790.35 | 5/17/2020 |
| Invoice 20 | 342033.86 | 5/18/2020 |
| Invoice 21 | 3228.91 | 5/19/2020 |
| Invoice 22 | 3228.91 | 5/20/2020 |
| Invoice 23 | 21526.08 | 5/21/2020 |
| Invoice 24 | 21526.08 | 5/22/2020 |
| Invoice 25 | 21526.08 | 5/23/2020 |
| Invoice 26 | 21526.08 | 5/24/2020 |
| Invoice 27 | 254.57 | 5/25/2020 |
| Invoice 28 | 608.08 | 5/26/2020 |
| Invoice 29 | 6114.84 | 5/27/2020 |
| Invoice 30 | 898.03 | 5/28/2020 |
| Invoice 31 | 160 | 5/29/2020 |
| Invoice 32 | 3953.4 | 5/30/2020 |
| Invoice 33 | 4135.42 | 5/31/2020 |
| Invoice 34 | 11477.8 | 6/1/2020 |
| Invoice 35 | 942.5 | 6/2/2020 |
| Invoice 36 | 3605.47 | 6/3/2020 |
| Invoice 37 | 18685.84 | 6/4/2020 |
| Invoice 38 | 6988.8 | 6/5/2020 |
| Invoice 39 | 7474.34 | 6/6/2020 |
| Invoice 40 | 32732.2 | 6/7/2020 |
| Invoice 41 | 18919.69 | 6/8/2020 |
| Invoice 42 | 964.51 | 6/9/2020 |
| Invoice 43 | 6872.54 | 6/10/2020 |
| Invoice 44 | 126.86 | 6/11/2020 |
| Invoice 45 | 27790.35 | 6/12/2020 |
| Invoice 46 | 342033.86 | 6/13/2020 |
| Invoice 47 | 3228.91 | 6/14/2020 |
| Invoice 48 | 3228.91 | 6/15/2020 |
| Invoice 49 | 21526.08 | 6/16/2020 |
| Invoice 50 | 21526.08 | 6/17/2020 |
| Invoice 51 | 21526.08 | 6/18/2020 |
| Invoice 52 | 21526.08 | 6/19/2020 |
***
Any other details needed, please let me know.
Thanks in advance.
Mous
3 Replies
- amitchandak
Super User
Mous007 , Try if these can work
calculate(sum(Table[Invoice Amount]), filter(Table, Table[due_date]<eomonth(Table[due_date],0))) calculate(sum(Table[Invoice Amount]), filter(Table, Table[due_date]<eomonth(Table[due_date],0)+1)) calculate(sum(Table[Invoice Amount]), filter(Table, Table[due_date]>eomonth(Table[due_date],0)))Based on Today
calculate(sum(Table[Invoice Amount]), filter(Table, Table[due_date]<eomonth(Today(),0))) calculate(sum(Table[Invoice Amount]), filter(Table, Table[due_date]<eomonth(Today(),0)+1)) calculate(sum(Table[Invoice Amount]), filter(Table, Table[due_date]>eomonth(Today(),0))) 
Mous007it can be considered close, thanks amitchandak
