Calculate and show measure only for latest quarter from period, selected in hierarchy slicer

[Anonymous]

Hi!

I hope somebody can help me with this.

I need to calculate and show measure only for latest quarter from period, selected in hierarchy slicer.

Examples:

1) if i'll select whole 2019 I need measure to display result only for Q4 2019.

2) if I'll select Q4 2018, Q1 2019, Q2 2019,Q3 2019 I need to display result only for Q3 2019

My current approach is:

1) Every record has it's own Year and date index [Last Q in selected period] (20191 - where "2019" is for year and "1" is for quarter;

2) When I select some period in slicer I use MAX([Last Q in selected period]) to detect the latest quarter;

3) And then i use  measures 99 Dynamic Tiles QUARTER and 99 Dynamic Tiles QUARTER to achive needed context.

The probplem is that measure shows some wrong average for for quarters (65 on prinscreen) instead of correct 75 (value fot Q4 2019) in spite of context which i created in Calculate function.

But when I use numbers instead of measures everything works well. Thanks in advance!

[Håkon]

Tusen takk!

[v-yuta-msft · Accepted solution]

@Anonymous ,

The logic in your measure is not correct, you can't just filter max year and max quarter simply. I would suggest you create an index column group by Quarter as below:

Index_Column =
RANKX (
    FILTER ( Table, Table[Date].[Quarter] = EARLIER ( Table[Date].[Quarter] ) ),
    Table[Date],
    ,
    Asc,
    DENSE
)

Then create a measure using dax like pattern below to achieve the aggregation value.

Result =
VAR Last_Quarter =
    CALCULATE ( MAX ( DateTable[Index_Column] ), ALLSELECTED ( DateTable ) )
RETURN
    CALCULATE (
        [measure],
        FILTER ( DateTable, DateTable[Index_Column] = Last_Quarter )
    )

Community Support Team _ Jimmy Tao

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

[Håkon]

Takk!

[Anonymous]

Hi @v-yuta-msft Community Support Team _ Jimmy Tao,

Thanks for your reply!

Unfortunately my result measure shows "Blank" (see screenshots below).

It's not clear for me how RANKX works - my DateTable has 4 years but maximum value in Index Column is 364 and there are a lot of duplicated values.  Looks like Index column restart counting from 1 every 3 months or every year.

My intention is to show the latest quarter from selected in slicer.

Many thanks for your help - it's much appreciated!

Regards,

Oleh

[Anonymous]

UPD. It worked well!

Thank You:)

[DavidMG82]

How did you make it worked? Im getting blanks too. Thanks ahead.