topic Re: How to get Quarter wise weekly average for past 5 quarters (Including current Quarter). in DAX Commands and Tips

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

Anonymous — Mon, 23 Sep 2024 10:25:24 GMT

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.

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

Selva-Salimi — Mon, 23 Sep 2024 11:51:27 GMT

Hi Anonymous

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.

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

Anonymous — Tue, 24 Sep 2024 02:45:19 GMT

Hi Anonymous ,

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.