cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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!

1 ACCEPTED SOLUTION
Community Support

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

6 REPLIES 6

Tusen takk!

Community Support

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

Takk!

Anonymous
Not applicable

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

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
Not applicable

UPD. It worked well!

Thank You:)

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.