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       ...
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!

     

     

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!