cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## Calculating change between periods

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

1 ACCEPTED SOLUTION
Community Support

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.

6 REPLIES 6
Community Support

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.

Community Champion

@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
Not applicable

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!

Community Champion

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

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

Community Champion

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors