calculate revenue for current and next quarter so it is freezed when selecting period

[bsas]

Hi Community,

I have list of people and their revenue for some period. I need to calculate revenue for current and next quarter based on today per each person but it should not change when I select period in slicer.

I used calculation below and with "All" total amount is ok but per person it is wrong. Without all revenue per person is ok but it is setsitive to period in slicer and changes when selecting period.

Curr Q and curr Q+1 revenue = 
CALCULATE(Revenue],
    FILTER(ALL('dwh FactOpportunity'),
        'dwh FactOpportunity'[closedate] >= STARTOFQUARTER('dwh DimDate'[Curr Q start]) &&
        'dwh FactOpportunityt'[closedate] <= EOMONTH(TODAY(),3+MOD(3-MONTH(TODAY()),3))))

[v-rzhou-msft · Accepted solution]

Hi @bsas 

To calculate the revenue for each person you may try this measure.

Curr Q and curr Q+1 revenue =
CALCULATE (
    [Revenue],
    FILTER (
        ALL ( 'dwh FactOpportunity' ),
        'dwh FactOpportunity'[closedate]
            >= STARTOFQUARTER ( 'dwh DimDate'[Curr Q start] )
            && 'dwh FactOpportunityt'[closedate]
                <= EOMONTH ( TODAY (), 3 + MOD ( 3 - MONTH ( TODAY () ), 3 ) )
            && 'dwh FactOpportunity'[People] = MAX ( 'dwh FactOpportunity'[People] )
    )
)

If this measure doesn't work, you can try my way.

Due to I don't know about your data model I build a sample to have a test.

My Sample Table:

I add a YearQtr column in this table to distinguish currenr qtr and next qtr.

My Measure:

Measure = 
VAR _CurQtr = YEAR(TODAY())*100+QUARTER(TODAY())
VAR _NextQtr = MINX(FILTER(ALL('dwh FactOpportunity'),'dwh FactOpportunity'[YearQtr]>_CurQtr),'dwh FactOpportunity'[YearQtr])
Return
SUMX(FILTER(ALL('dwh FactOpportunity'),'dwh FactOpportunity'[YearQtr] in {_CurQtr,_NextQtr}&&'dwh FactOpportunity'[People]=MAX('dwh FactOpportunity'[People])),'dwh FactOpportunity'[Revenue])

Result is as below.

The slicer won't impact the result.

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[CNENFRNL]

Hi, @bsas , a best practice is to apply ALL() to specific column(s) in order to keep other filters on the same table effective; you might want tweak your measure this way,

Curr Q and curr Q+1 revenue =
CALCULATE (
    [Revenue],
    FILTER (

        ALL ( 'dwh FactOpportunity[closedate]' ),

        'dwh FactOpportunity'[closedate]
            >= STARTOFQUARTER ( 'dwh DimDate'[Curr Q start] )
            && 'dwh FactOpportunityt'[closedate]
                <= EOMONTH ( TODAY (), 3 + MOD ( 3 - MONTH ( TODAY () ), 3 ) )
    )
)

[bsas]

Thanks but this don't work for my case