Need to calculate next 3 quarters data along with selected quarter in the filter

[arunalle]

Hi friends,

I have a requirement that I need to calculate next 3 quarters data along with selected quarter in the filter in a matrix visual. I have the quarter column in the form of "Q1 FY22". The fields that I used in the matrix visual are 

Rows : Category

Columns : Sales dates
Values : Sales amount

Example :1. If I select Q3 FY22 in the filter then need to calculate the sales amount for Q3 FY22, Q4 FY22, Q1 FY23, Q2 FY23.

Example :2. If I select Q2 FY24 in the filter then need to calculate the sales amount for Q2 FY24, Q3 FY24, Q4 FY24, Q1 FY25.

please suggest me with a perfect dax expression. thanks in advance

[v-yiruan-msft · Accepted solution]

Hi  @arunalle ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a quarters dimenstion table as below(DO NOT create any relationship with the fact tables)

2. Create a calculated column to get the number in quarter field

QNum = VALUE ( RIGHT ( 'Table'[Quarter], 2 ) & MID ( 'Table'[Quarter], 2, 1 ) )

3. Create a measure as below to get the sales amount

Measure = 
VAR _selquarter =
    SELECTEDVALUE ( 'Quarters'[Quarter] )
VAR _selqnum =
    CALCULATE (
        MAX ( 'Table'[QNum] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Quarter] = _selquarter )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales amount] ),
        FILTER (
            'Table',
            'Table'[QNum] >= _selqnum
                && IF (
                    MID ( _selquarter, 2, 1 ) = "1",
                    'Table'[QNum] <= _selqnum + 3,
                    'Table'[QNum] <= _selqnum + 9
                )
        )
    )

4. Create a slicer using the quarter field in dimension table

5. Create a matrix visual

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Greg_Deckler]

@arunalle So, the best way to address this would be to have a Dates table. You would build a relationship to your fact table. In the Dates table you could create a sequential quarter value similar to Sequential: Sequential - Microsoft Power BI Community

Then the last 3 quarters is always the current selected quarter minus 3 or 2 depending on how you want it to work.