Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
darshaningale
Resolver II
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
v-eqin-msft
Community Support
Community Support

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)

Eyelyn9_0-1630545666527.png

 

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 )

dynamic previous quarter2.gif

 

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.

View solution in original post

11 REPLIES 11
v-eqin-msft
Community Support
Community Support

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

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

v-eqin-msft
Community Support
Community Support

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

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
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
v-eqin-msft
Community Support
Community Support

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)

Eyelyn9_0-1630545666527.png

 

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 )

dynamic previous quarter2.gif

 

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
Community Support
Community Support

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:

dynamic previous quarter.gif


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.

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 }

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 }

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

 

lbendlin
Super User
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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.