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

Reply
cpdanielmc21
Helper I
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

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

 

 

v-xuding-msft_0-1597655869696.png

 

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.

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
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...


Follow on LinkedIn
@ 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...
amitchandak
Super User
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

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.

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:

DateMonthYearMYQQY
8/14/2020Aug2020Aug'202033Q2020
8/15/2020Aug2020Aug'202033Q2020

 

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

PeriodYearDateAccountAmount
320203/31/2020Revenue120
620206/30/2020Revenue200
920209/30/2020Revenue250
12202012/31/2020Revenue350

 

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

 

 

v-xuding-msft_0-1597655869696.png

 

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.
ToddChitt
Super User
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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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