Apr 19 2021 11:52 AM
Hi,
My first post here and I hope somebody can help me out. I apologise if there is already a solution in this forum for my question. But I have been looking through hours of YouTube videos trying to find the answer but without luck.
Anyhow, I will try to explain what I need help with below. Also, please see attached example file for reference. Maybe it will make it clearer.
It would really be fantastic if somebody could help be out and I would be very grateful. ‘
Thanks in advance!
Apr 19 2021 12:13 PM
Maybe that will help you a little further
Thank you for your patience and time.
Wish you a nice day / night with lots of health, joy and love.
Nikolino
I know I don't know anything (Socrates)
Apr 19 2021 12:37 PM
Perhaps something like
=SUMIFS(
TblInköp[[First payment amount]:[First payment amount]],
TblInköp[[Date first payment]:[Date first payment]],">="&P$2,
TblInköp[[Date first payment]:[Date first payment]],"<="&EOMONTH(P$2,0)
)
for first payment amount and similar for the rest. Please check attached.
Apr 19 2021 01:36 PM
Apr 19 2021 02:09 PM
Thanks a lot Sergei!
This was very helpful. I think I might use your solution. But I think it would be even better if I could use pivot table (or power pivot if needed) because then I do not need to do the formula every time. Also, with a pivot table I would be able to easily change and group the date from days to, month or year or quarter etc.
Another advantage if there was a way to use a pivot table would be that I would then also be able to easily calculate the delivered quantity for each product for, say, every month. I know this was not a request in my first problem description, so I did not except anyone to solve this issue for me.
Maybe my idea of solving this using pivot table is wishful thinking. But if you do have another solution (with or without pivot table) which would also help me with the other challenge mentioned above, I would be even more grateful! Please see new attachment for reference.
Thanks in advance!
Apr 19 2021 04:26 PM
If you need something like this
when creating PivotTable we need to add data to data model. First thing here we need separate Date (Calendar) table linked with our main table. I created with by Power Pivot but much better to use Power Query, there are a lot of patterns for that.
After that we create relationships for each of dates in main table
and build measures as
First payment:=CALCULATE (
SUM ( 'TblInköp'[First payment amount] ),
USERELATIONSHIP ( 'Calendar'[Date], 'TblInköp'[Date first payment] )
)
Final payment:=CALCULATE (
SUM ( 'TblInköp'[Final payment amount] ),
USERELATIONSHIP ( 'Calendar'[Date], 'TblInköp'[Date final payment] )
)
Qty delivered:=CALCULATE (
SUM ( 'TblInköp'[Qty] ),
USERELATIONSHIP ( 'Calendar'[Date], 'TblInköp'[Date delivery] )
)
Payment:=[First payment]+[Final payment]
which we use in PivotTable.
Please check in attached file.
Apr 20 2021 11:40 PM
Thanks once again! I am very grateful for your help. But I still need some more help. I apologise for not being able to explain what I want to accomplish better. But I have made one more try. Please see attached document and the page “end result explanation”. Also, please see screen shot below for a quick preview.