cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Resolver II

## Show previous three quarters based on quarter selected in slicer.

I have a date dimension and a fact table.

I have FiscalYear, FiscalQuarter, FiscalMonth in the date dimension and a measure in the fact table.

I want to create a column chart where the FiscalQuarters/FiscalMonth would be shown on the XAxis.

If I select Quarter2(this year) in the slicer , the XAxis should show Quarter2 & Quarter1 of this year and Quarter 4 of last year.

1 ACCEPTED SOLUTION
Community Support

Firstly sorry for my method may be a bit difficult to understand and seems to weird😅 Let me explain to you:

According to your description, you want to show the previous 3 quarters in a visual, so my basic idea is to calculate the quarter-diff and if it in a special range, then set a flag=1.

The quarter-diff is a little complex as I thought before:

 1 202104 202103 202102 Diff=0,1,2 2 202103 202102 202101 Diff=0,1,2 3 202102 202101 202004 Diff=0,1,98 4 202101 202004 202003 Diff=0,97,98

But now, thanks for @lbendlin suggestion, I realized we could add a Index column to limit the quarter-diff to between 0, 1, and 2 simply.

So please use RANKX() to add a Index column to ForSlicer table:

``Index = RANKX('ForSlicer',[YearQuarter],,ASC,Dense)``

Then change the Flag measure:

``````Flag =
VAR _max =
SELECTEDVALUE ( ForSlicer[YearQuarter] )
VAR _min =
CALCULATE (
MAX ( 'ForSlicer'[YearQuarter] ),
FILTER (
ALL ( 'ForSlicer' ),
'ForSlicer'[Index]
= SELECTEDVALUE ( ForSlicer[Index] ) - 2
)
)
VAR _yearQuarter =
MAX ( 'Date Dimension'[FiscalYear] ) * 100
+ MAX ( 'Date Dimension'[FiscalQuarter] )
RETURN
IF ( _yearQuarter <= _max && _yearQuarter >= _min, 1, 0 )
``````

In addition, as you mentioned: This is somewhat different to my data and format. please provide me with more details about your table or share me with your pbix file after removing sensitive data.

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

11 REPLIES 11
Community Support

Since The requirement has changed, I suggest you create a new post for further help.

And If my second post makes sense to you , please consider Accept it as the solution to help the other members find it more quickly.😀 Thanks in advance.

Best Regards,
Eyelyn Qin

Resolver II

Thank you for your support. Your solution works for previous quarters and forecast quarters also if the logic is changed accordingly.

Community Support

Could you tell me if my second post helps you a little? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.Hope to hear from you‌‌😀

Best Regards,
Eyelyn Qin

Resolver II

Hi Evelyn,

thank you for that detailed explanation.

The requirement has changed. i have to show the forecasted periods(not quarters) now and i am trying to understand how can i do that with your solution..

The solution looks promising but i am yet to implement it.

P.S. : i cannot send the pbix file as the data model is too complicated and too time consuming to remove the sensitive data.

Super User

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Firstly sorry for my method may be a bit difficult to understand and seems to weird😅 Let me explain to you:

According to your description, you want to show the previous 3 quarters in a visual, so my basic idea is to calculate the quarter-diff and if it in a special range, then set a flag=1.

The quarter-diff is a little complex as I thought before:

 1 202104 202103 202102 Diff=0,1,2 2 202103 202102 202101 Diff=0,1,2 3 202102 202101 202004 Diff=0,1,98 4 202101 202004 202003 Diff=0,97,98

But now, thanks for @lbendlin suggestion, I realized we could add a Index column to limit the quarter-diff to between 0, 1, and 2 simply.

So please use RANKX() to add a Index column to ForSlicer table:

``Index = RANKX('ForSlicer',[YearQuarter],,ASC,Dense)``

Then change the Flag measure:

``````Flag =
VAR _max =
SELECTEDVALUE ( ForSlicer[YearQuarter] )
VAR _min =
CALCULATE (
MAX ( 'ForSlicer'[YearQuarter] ),
FILTER (
ALL ( 'ForSlicer' ),
'ForSlicer'[Index]
= SELECTEDVALUE ( ForSlicer[Index] ) - 2
)
)
VAR _yearQuarter =
MAX ( 'Date Dimension'[FiscalYear] ) * 100
+ MAX ( 'Date Dimension'[FiscalQuarter] )
RETURN
IF ( _yearQuarter <= _max && _yearQuarter >= _min, 1, 0 )
``````

In addition, as you mentioned: This is somewhat different to my data and format. please provide me with more details about your table or share me with your pbix file after removing sensitive data.

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

Community Support

You could kindly follow @lbendlin 's suggestion.

Here is my example you could refer to:

1.Create a new table for slicer:

``ForSlicer = DISTINCT( SELECTCOLUMNS('Date Dimension',"YearQuarter",[FiscalYear]*100+[FiscalQuarter]))``

2.Create a measure for filter:

``````Flag =
VAR _sele =
SELECTEDVALUE ( ForSlicer[YearQuarter] )
VAR _yearQuarter =
MAX ( 'Date Dimension'[FiscalYear] ) * 100
+ MAX ( 'Date Dimension'[FiscalQuarter] )
RETURN
IF ( _sele - _yearQuarter IN { 0, 1, 2, 97, 98 }, 1, 0 )``````

3. Apply the filter to visual-level filter, set as "is 1". The final output is shown below:

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

Resolver II

Hi,

How can i do this for Periods and not Quarters ? Because Period is my lowest level of granularity.

what do you mean by  _sele - _yearQuarter IN { 0, 1, 2, 97, 98 }

Super User

This is caused by the fancy date math that @v-eqin-msft is doing.

``[FiscalYear]*100+[FiscalQuarter]``

then she checks if the difference between the selected yearquarter and the row yearquarter is in the list of values.  If you truly want the "previous three quarters" only then you should use an index column for that yearquarter and the formula should be

_sele - _yearQuarter IN { 1, 2, 3 }

Resolver II

This is somewhat different to my data and format. But i will try this and let you know.

Super User

Your slicer will need to be fed by a separate, disconnected table (containing only the YearQuarter strings, for example).  Then you can adjust your measure to calculate your fact data accordingly.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors