Calculate QTD from running total

[cpdanielmc21]

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 - Revenue - 200

Q3 - Revenue - 250

Q4 - Revenue - 350

So what I wish to get is just the deltas, notice that if date is equal to Q1, I should get the same number

Q1 - Revenue - 120

Q2 - Revenue - 80

Q3 - Revenue - 50

Q4 - Revenue - 100

In my data there are several years and only the end of month, I have also a Date table that is linked to my data table with the end of month column.  I tryed TOTALQTD function, but doesnt work because my data comes acumulated not per month.

Thanks in advance if you can help with a measure or column formula because I need this deltas to create bar charts

[Greg_Deckler]

@cpdanielmc21 - You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak]

@cpdanielmc21 , if you have date and qtr level data you can use datesqtd to get this qtr vs last qtr data and sunbract it.

Now, in this case, create a rank on qtr year and the try like this

Period Rank = rankx(all(Date),Date[Qtr-Year],,asc,dense) // It always bettwer os have separate date/qtr table

This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))

Diff = [This Period] -[Last Period ]

the same approach is discussed for Week and work for any nonstandard period

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

In case you have Date

https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

In case you want to create a date from Qtr No and year(the first couple of min has that)

https://www.youtube.com/watch?v=yPQ9UV37LOU

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[cpdanielmc21]

Hi @amitchandak Just a quick question,

should period rank be a new column in one of my tables or a measure? Also, in that DAX formula, what is Date[Qtr-Year]?

My date table is currently like this:

Date	Month	Year	MY	Q	QY
8/14/2020	Aug	2020	Aug'2020	3	3Q2020
8/15/2020	Aug	2020	Aug'2020	3	3Q2020

(and so on) My date table is "automated" with a DAX that looks for first and last date of my accounting data.

And my Accounting Data base is like this:

Period	Year	Date	Account	Amount
3	2020	3/31/2020	Revenue	120
6	2020	6/30/2020	Revenue	200
9	2020	9/30/2020	Revenue	250
12	2020	12/31/2020	Revenue	350

[v-xuding-msft · Accepted solution]

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]

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.