How to get Quarter wise weekly average for past 5 quarters (Including current Quarter).

[Chari]

HI All....
How to get Quarter wise weekly average for past 5 quarters 
Example : If current quarter is Q3 2024, data should be displayed for Q3 2024, Q2 2024, Q1 2024, Q4 2022, Q3 2023.
 
like below table i have to show the weekly avgs.

[Anonymous]

Hi @Chari ,

First create a date table:

DateTable = 
ADDCOLUMNS (
    CALENDAR (DATE(2023, 1, 1), DATE(2024, 12, 31)),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT(QUARTER([Date]), "0"),
    "YearQuarter", FORMAT([Date], "YYYY") & "Q" & FORMAT(QUARTER([Date]), "0"),
)

Create a measure to filter the data for the last 5 quarters:

Last5Quarters = 
CALCULATE (
    [YourWeeklyAverage],
    FILTER (
        ALL('DateTable'),
        'DateTable'[YearQuarter] IN {
            FORMAT(EDATE(TODAY(), -3 * 0), "YYYY") & "Q" & FORMAT(QUARTER(TODAY()), "0"),
            FORMAT(EDATE(TODAY(), -3 * 1), "YYYY") & "Q" & FORMAT(QUARTER(EDATE(TODAY(), -3 * 1)), "0"),
            FORMAT(EDATE(TODAY(), -3 * 2), "YYYY") & "Q" & FORMAT(QUARTER(EDATE(TODAY(), -3 * 2)), "0"),
            FORMAT(EDATE(TODAY(), -3 * 3), "YYYY") & "Q" & FORMAT(QUARTER(EDATE(TODAY(), -3 * 3)), "0"),
            FORMAT(EDATE(TODAY(), -3 * 4), "YYYY") & "Q" & FORMAT(QUARTER(EDATE(TODAY(), -3 * 4)), "0")
        }
    )
)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Selva-Salimi]

Hi @Chari 

you can write a column as follows:

Rank_Quarter = calculate(count('Date'[Year_quarter]), filter('Date' , 'Date'[Year_quarter] >= EARLIER('Date'[Year_quarter])))

and then add this column to visual level filter and set it between 1 to 5.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.