Log In
Register
Home
Active Member
November 25, 2020
Offline
Dear Mynda and Philip,
thanks a lot that I am able to post my problem here:) I am a PQ and Powerpivot Newbie.
I would love to get a solution in Power Query that reverse my monthly YTD values to monthly vlaues and consider the beginning of a new FY in October.
Every month I get the ytd profit & loss data for several companies for each account and I merge it in PQ. In classical excel I have a working formula to show me only the monthly values but I really want a a solution in PQ. Account numbers can be identical for several companies
Please find my example data attached. Thank you so much.
Kind regards,
Dennis
October 5, 2010
Offline
Hi Dennis,
Please see attached file. This works out the monthly change based on the Account Number and the Customer.
I'm not sure that Row 20 is correct. The Acct Num and Cust are the same as the previous row, but date is Aug, should it be Oct?
Regards
Phil
Active Member
November 25, 2020
Offline
Hi Phil,
thanks a lot for your great work and answer.
Very elegant solution...Okay, functions/parameter in PQ are new to me. Only one thing: October is new fiscal year. So in Oct. the ytd and monthly values should be the same.
Yes, you are tight. Row 20 should be October Only exemplary data from my side.
Thanks a lot for your very useful help.
Regards,
Dennis
October 5, 2010
Offline
Thanks Dennis.
I've modified the function to reset in October - see attached.
Regards
Phil
Active Member
November 25, 2020
Offline
Dear Phil,
I hope you are well?! I would really love to integrate your elegant solution for my new project.
Could you please be so kind and help me one more time? Either I'm doing something wrong or the function does not seem to work quite correctly yet, because if a new account is added for example off 01.10. the monthly change must also correspond to the YTD value or not? Now there is a 0.
Thanks again for your help!
BR Dennis
1 Guest(s)
