Irregular Quarter Comparison

[Anonymous]

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

[v-xulin-mstf]

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]

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]

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!

[Anonymous]

Company     Deptt.   Quarter    Salary

ABC              Sales      2020Q1          10000

ABC              Sales      2020Q2           20000

ABC              Marketing  2020Q1       20000

ABC               Marketing  2020q3       50000

DEF              Accounts     2020q1        400000

DEF              Accounts     2020q2         400000

Final results should be when Q3 & q1 is selected

ABC      Sales              null      10000

ABC      Marketing     50000   20000

DEF       Accounts       400000   null

[v-xulin-mstf · Accepted solution]

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!

[Anonymous]

Perfect, it worked!

[Greg_Deckler]

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