Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

1 ACCEPTED SOLUTION
Community Support

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])``

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User

@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...

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

@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

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.

Helper I

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

Community Support

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])``

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

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.

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Helpful resources

Announcements

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors