Forum Discussion

cpdanielmc21's avatar
cpdanielmc21
Icon for Helper I rankHelper I
6 years ago
Solved

Calculate QTD from running total

Hi community I hope you can help me on this one. I have accounting data with amounts that come acumulated, and I need to calculate the QTD from it. Here is an example: Q1 - Revenue - 120 Q2 - Rev...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi cpdanielmc21 ,

     

    I create two calculated columns that you could have a try.

     

    Current Amount =
VAR lastrow =
    CALCULATE (
        MAX ( 'Table'[Amount] ),
        FILTER ( 'Table', 'Table'[Period] < EARLIER ( 'Table'[Period] ) 
                           && 'Table'[Year] = EARLIER ( 'Table'[Year] ))
    )
RETURN
    'Table'[Amount] - lastrow

    QTD = TOTALQTD(SUM('Table'[Current Amount]),'Date'[Date])

     

     

     

ToddChitt's avatar
ToddChitt
Icon for Super User rankSuper User
6 years ago

I guess I don't really understand the issue. Your running total IS a QTD number already. Just report that number.

 

But I also recognize that this data is not in an ideal format. One brute force approach in Power Query would be:

1 Strip the "Q" to get just the integer

2 Duplicate the query

3 Add 1 from to the quarter integer

4 Join (merge, left outer) the two tables on Year = Year AND Quarter = [Quarter plus 1]

5 Create a new column with the formula of [QTD Amount] minus [QTD Amount of Q+1]

 

For Q1, there will be no matching value because there was no Q0+1 to make Q1.

But for Q2, you will take the running total and subtract the running total from Q1.