Apples to Apples Quarterly Comparison

isam2003 — Thu, 13 Apr 2023 19:16:29 GMT

Hello:

I am calculating a dashboard that captures the latest business KPIs.

Given FYE of June, I am currently trying to ensure that the summations of quarterly KPIs by fiscal year is consistent. I have sales data only for the first two quarters of FY2023 and all four quarters of FY2022. The chart below is not a good comparison because it includes four quarters of data for FY2022 and only two quarters of FY2023. I would like it to reflect only the first two quarters of FY2022 also.

How can I adjust the Dashboard set up so that it adjusts automatically when Q3 FY2023 sales data is available? I am trying to avoid the use of filters on the visual since that would involve manual intervention.

Any help would be much appreciated. Thanks in advance.

VAR __Type = MAX('Year_Type'[Year_Type])

VAR __Year = MAX('Years'[Year])

VAR __Result =

IF(

__Type = "Fiscal",

SUMX(FILTER('Data', [FYear] = __Year),[Value]),

SUMX(FILTER('Data', [CYear] = __Year),[Value])

)

RETURN

__Result

Re: Apples to Apples Quarterly Comparison

Greg_Deckler — Thu, 13 Apr 2023 20:17:08 GMT

isam2003 Probably best to create an offset or Prior Quarter Date in your Date table like so:

Prior Quarter Date = VAR __Date = [Date] VAR __PQDate = EOMONTH(__Date, -3) VAR __PriorDate = DATE(YEAR(__PQDate), MONTH(__PQDate), DAY(__Date)) RETURN __PriorDate

You can then follow your preferred poison in this video for Prior Quarter To Date (PQTD):

topic Re: Apples to Apples Quarterly Comparison in DAX Commands and Tips

Apples to Apples Quarterly Comparison

Re: Apples to Apples Quarterly Comparison