Forum Discussion
Helper IHelp With Creating Cumulative Total Based on Two Variables
Hi Community,
I'm looking for some help on what I originally thought was going to be easy, but turns out I've been struggling with -- hope someone is up to the challenge.
Excel Workbook is a workbook which I think highlights what is desired very simply. The first tab is what we are starting with in Power Query. It's in tabular format, lists all the dates in a given month, and by GL Account, what transaction amounts were. The values and accounts showing on 3/1/19 represent beginning balances for all desired GL accounts.
The second tab is the output that i'm hoping to be able to create in PowerQuery, which cumulatively summarizes the values by both gl account and date in tabular format.
Excited to see if anyone is able to figure it out, thanks!
8 Replies
swan1099I forgot to add that I found this YouTube Video which gets close, but only seems to be able to calculate cumulatively for one variable:
https://www.youtube.com/watch?v=2P658-WDJAQ
Hi swan1099 ,
You can try to use following measure formula if it works for your scenario:
Measure = VAR _start = variable1 VAR _end = variable2 RETURN CALCULATE ( SUM ( Table[NetGLValue] ), FILTER ( ALLSELECTED ( Table ), [Date] >= _start && [Date] <= _end ), VALUES ( Table[GLAccount] ) )Regards,
Xiaoxin Sheng
- swan1099
Hi Xiaoxin,
Appreciate the reply, but I was hoping to accomplish this in PowerQuery/Get & Transform -- not DAX?
- Ashish_Mathur
Super User
Hi,
Your question is not clear. There are 2 instances of GL code 21650 - same number but opposite signs. In the output sheet, the negative number goes from March 1 to March 7 - Why? What result are you expecting for GL code 21671 and 21672?
- swan1099
Hi Ashish,
Appreciate you taking a look, and sorry for any confusion.
On March 1st, GL code 21650 had a starting credit balance (is a loan/liability acct) of -536,249.65. You are able to see a debit (positive) balance of 536,249.65 on March 8th, because that loan was paid off on that day. If you take a look at the second tab for this GL, "Desired Output," (rows 126-156), you will see what the cumulative total should look like if we found a solution in PowerQuery to get the first tab to transform into the desired result. Same thing applies with all of the other GL accounts.
Let me know if there's something else I could elaborate on further -- thanks for taking a look.
- Ashish_Mathur
