cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Calculating change between periods

Hi everyone! Thanks in advance for your help.

 

I have some data that looks like this:

QuarterSales (Beg)
Q1100
Q2125
Q3150

 

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:

QuarterSales (Beg)Sales (End)Change
Q110012525
Q212515025
Q3150TBDNA

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @Anonymous 

 

You can try the following methods.

Column:

Quarter num = RIGHT([Quarter],1)

Change its data format to number.

vzhangti_0-1659693315136.png

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

vzhangti_1-1659693394873.png

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.

View solution in original post

6 REPLIES 6
v-zhangti
Community Support
Community Support

Hi, @Anonymous 

 

You can try the following methods.

Column:

Quarter num = RIGHT([Quarter],1)

Change its data format to number.

vzhangti_0-1659693315136.png

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

vzhangti_1-1659693394873.png

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
Community Champion
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! 

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

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Power BI Fabric Summit Carousel

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