Calculating change between periods

[Anonymous]

Hi everyone! Thanks in advance for your help.

I have some data that looks like this:

Quarter	Sales (Beg)
Q1	100
Q2	125
Q3	150

However, I want to compare Sales across quarters. The data above is for the beginning of each quarter. The following quarter's data would be the end of that quarter. Is there an easy way to achieve this? Thank you!! 

End State:

Quarter	Sales (Beg)	Sales (End)	Change
Q1	100	125	25
Q2	125	150	25
Q3	150	TBD	NA

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Column:

Quarter num = RIGHT([Quarter],1)

Change its data format to number.

Sales (End) =
CALCULATE (
    SUM ( 'Table'[Sales (Beg)] ),
    FILTER ( 'Table', [Quarter num] = EARLIER ( 'Table'[Quarter num] ) + 1 )
)

Change = 
IF([Sales (End)]<>BLANK(), [Sales (End)]-[Sales (Beg)],BLANK())

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SpartaBI]

@Anonymous sure, do you have a date dimenstion table? (or maybe your data is flat and you have one row per quarter so I won't take you all the way and just solve this, but important to say this will not be a generic and best practice solution as with a date table). 
Also, do you want it as a measure to use in a matrix visual or as a calculated columns?

[Anonymous]

I do not have a date dimension table (not sure what that is). The time reference (quarters) is text in my underlying data (does not resemble time/date at all). The underlying data is lots of row per quarter. The goal is to graph the change between quarters. Thanks so much! 

[SpartaBI]

amm...so you better to do it right from the start. 
I suggest 1st try to look at this comprehensive article which walks you through all the process of all the time intteligence fucntions including of course the Q/Q you are loooking for.
https://www.daxpatterns.com/standard-time-related-calculations/

If you won't succeed then I will need you to share a sample of the real file to solve it for you.

[Anonymous]

It's not actually a Q/Q comparison. My time periods are completley arbitrary and in text format. It's like comparing red versus yellow versus orange. I was thinking I'd have to create a new table that states the beginning and end of each period but I'm not sure what measure/column to create to then calculate the change

[SpartaBI]

I think then in this case you need to share the actual file and explain exactly what is the logic. Better to also open a new question to get more fresh people to look at it