Show previous three quarters based on quarter selected in slicer.

[darshaningale]

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.

[v-eqin-msft]

Hi @darshaningale ,

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

[darshaningale]

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

[v-eqin-msft]

Hi @darshaningale ,

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

[darshaningale]

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.

[Ashish_Mathur]

Hi,

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

[v-eqin-msft · Accepted solution]

Hi @darshaningale ,

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.

[v-eqin-msft]

Hi @darshaningale ,

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.

[darshaningale]

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 }

[lbendlin]

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 }

[darshaningale]

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

[lbendlin]

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.