Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

 

Help appreciated
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        SpartaBI
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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.