Help With Creating Cumulative Total Based on Two Variables

[swan1099]

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!

[Ashish_Mathur]

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 · Accepted solution]

Hi,

You may download my Excel solution workbook from here.

Hope this helps.

[swan1099]

Very clever -- appreciate it!

---

@Ashish_Mathur wrote:

Hi,

You may download my Excel solution workbook from here.

Hope this helps.

---

[Ashish_Mathur]

You are welcome.

[swan1099]

I 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

[v-shex-msft]

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?