dynamically showing last 5 financial quarters

[bhaskarasalla]

Hello,
Can anyone help me with below issue
I have requirement where my client want to see data for last 5 financial quater.
Client financial year start from feb 01 to jan 31.

requirement is if current fQ is 2019 Q3- then chart should be like

Finacial_year_quarter   values
2019 q3                       some values
2019 q2                       some values
2019 q1                       some values
2018 q4                       some values
2018 q3                      some values

Thanks in Advance

[sturlaws]

Hi @bhaskarasalla ,

assuming you have a date dimension, you can solve this by adding a few columns to your dimension.

First create a column to identify current date:

currentDate = IF('Dim date'[Date]=TODAY();1;0)

Then create a column to identify current quater:

currentQuater =
CALCULATE (
    SUM ( 'Dim date'[currentDate] );
    FILTER (
        'Dim date';
        'Dim date'[quater] = EARLIER ( 'Dim date'[quater] )
            && 'Dim date'[year] = EARLIER ( 'Dim date'[year] )
    )
)

Then we need a column called quaterYearNumber, which is unique number for each quater-year combination, which make it possible to do some simple arithmetics:

quaterYearNumber = ('Dim date'[year]-2016)*4+'Dim date'[quater]

Then we finally create a column called Last 5 quaters flag:

Last 5 quaters flag=
VAR _current =
    CALCULATE (
        MIN ( 'Dim date'[quaterYearNumber] );
        FILTER ( 'Dim date'; 'Dim date'[currentDate] = 1 )
    )
RETURN
    IF (
        'Dim date'[quaterYearNumber] > _current - 5
            && 'Dim date'[quaterYearNumber] <= _current;
        1;
        0
    )

You can add [Last 5 quaters flag] to the visual filter area of any visual, and set it equal to 1, and it will show only the last 5 quaters. And it will be dynamic, it will always reflect on the current date.