Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Irregular Quarter Comparison

Hi,

 

I have data for 4 quarters. The idea is to compare one particular column's value with the another.

 

Quarter             Value

2020q1             100

2020q2             200

2020q3             300

2020q4             400

 

I want to identify Current Quarter which should be the maximum of the ones i have selected and previous quarter which should be the minimum. So if i have 4 quarters 2020-Q1,Q2,Q3,Q4 and using slicer on quarter, if i select on q2 and q4

it should set CQ as value of q4 and for PQ it should set as of q2.

 

when q2 and q4 are selected

quarter   cq    pq

2020q2   200  blank

2020q4   400   200

 

 

 

 

Need Help
Show and Tell
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Anonymous,

     

    I'm not clear about the red mark:

    When Q1 and Q2 selected, why aren't the places marked in red returning null values?

     

    Try measure as:

    CQ = 
var quar=
MAXX(
    ALLSELECTED('Table'[Quarter]),
    'Table'[Quarter])
return
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Quarter]=quar && 'Table'[Company]=MAX('Table'[Company]) && 'Table'[Deptt]=MAX('Table'[Deptt])
    ),
    'Table'[Value]
)
    PQ = 
var _tab=
SUMMARIZE(
    ALLSELECTED('Table'),
    'Table'[Index],
    'Table'[Quarter],
    'Table'[Value],
    'Table'[Company],
    'Table'[Deptt]
)
return
MAXX(
    FILTER(
        _tab,
        [Index] < MAX('Table'[Index]) && 'Table'[Company]=MAX('Table'[Company]) && 'Table'[Deptt]=MAX('Table'[Deptt])
    ),
    [Value]
)

     Here is the output:

     

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

     

     

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Anonymous Well, the previous quarter should be the MAXX of the values that are < the MAX (current quarter)

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi Anonymous

     

    You need add a index column:

    Try measure as:

    PQ = 
var _tab=
SUMMARIZE(
    ALLSELECTED('Table'),
    'Table'[Index],
    'Table'[Quarter],
    'Table'[Value]
)
return
MAXX(
    FILTER(
        _tab,
        [Index] < MAX('Table'[Index])
    ),
    [Value]
)

    Create column as:

    CQ = 
CALCULATE(
    MAX('Table'[Value]),
    ALLSELECTED('Table'[Quarter])
)

    Here is the output:

    The pbix is attached.

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi,

       

      Looks like your solution is correct although i am having trouble implementing it.

      It is giving me same value for each PQ when implemented.

       

      PQ_GO_1 =
      var _tab=
      SUMMARIZE(
      ALLSELECTED('Data files'),
      'Data files'[Index],
      'Data files'[Quarter],
      'Data files'[company],
      'Data files'[dept],
      'Data files'[salary]
      )
      return
      MAXX(
      FILTER(
      _tab,
      [Index] < MAX('Data files'[Index])
      ),
      [salary]
      )
       
       
      I want to summarize it using company & deptt and get the total salary of each company & deptt wise. However, it is showing same value.
      Any solution for this?
      • v-xulin-mstf's avatar
        v-xulin-mstf
        Community Support
        5 years ago

        Hi Anonymous,

         

        Could you provide your sample data and expected output?

         

        Best Regards,

        Link

         

        Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!