YOY Growth Comparison tips

[GanesaMoorthyGM]

Hii community,

I was working on showing growth over last year i have past 5 years of data dumbed into my db

this was my calendar table

Calendar (P1) =

VAR MinDate = CALCULATE(MIN(fact_sales[DateOnly]), ALL(fact_sales))

VAR MaxDate = CALCULATE(MAX(fact_sales[DateOnly]), ALL(fact_sales))

RETURN

ADDCOLUMNS(

    CALENDAR(MinDate, MaxDate),

    "Year", YEAR([Date]),

    "MonthNumber", MONTH([Date]),

    "MonthName", FORMAT([Date], "MMMM"),

    "Quarter", "Q" & FORMAT(ROUNDUP(MONTH([Date])/3, 0), "0"),

    // FiscalYearEnd calculated inline

    "FiscalYearEnd", YEAR([Date]) + IF(MONTH([Date]) >= 4, 1, 0),

    // FiscalYear calculated inline

    "FiscalYear", "FY" & FORMAT(YEAR([Date]) + IF(MONTH([Date]) >= 4, 1, 0), "00"),

    // FiscalYearCode inline

    "FiscalYearCode", "FY" & RIGHT(FORMAT(YEAR([Date]) + IF(MONTH([Date]) >= 4, 1, 0), "0000"), 2),

    // FiscalYearPeriod inline using FiscalYearEnd and FiscalYearStart logic directly

    "FiscalYearPeriod",

        FORMAT(

            (YEAR([Date]) + IF(MONTH([Date]) >= 4, 1, 0)) - 1,

            "0000"

        ) & "-" &

        FORMAT(

            YEAR([Date]) + IF(MONTH([Date]) >= 4, 1, 0),

            "0000"

        ),

    "FiscalMonth", MOD(MONTH([Date]) - 4, 12) + 1,

    "FiscalQuarter", "Q" & ROUNDUP(((MOD(MONTH([Date]) - 4, 12) + 1) / 3), 0),

    "YearMonth", FORMAT([Date], "YYYYMM"),

    "DayOfWeek", WEEKDAY([Date], 1),

    "DayName", FORMAT([Date], "dddd")

)

Please note my Fiscal Year lies Between april to march

this is my last year measure

Growth =

VAR CY=

    [Total Sales Value in Cr FSM]

 VAR LY =   CALCULATE(

        [Total Sales Value in Cr FSM],

        DATESBETWEEN('Calendar (P1)'[Date],

        EDATE(MIN('Calendar (P1)'[Date]),-12),

        EOMONTH(MAX(fact_sales[DateOnly]),-12)

)

 )

VAR CHECK = CY && LY

 RETURN

     LY

and this is the growth %
Growth % =
VAR CY=
[Total Sales Value in Cr FSM]
VAR LY = CALCULATE(
[Total Sales Value in Cr FSM],
SAMEPERIODLASTYEAR('Calendar (P1)'[Date])
)
VAR CHECK = CY <> BLANK()&& LY <> BLANK()
RETURN
IF(CHECK,DIVIDE(CY,LY)-1 )

the thing here is i wanto focus on multiple metrics for growth comparison like Year, Country(this year,last year), similarly for Location code etc..

What my nuances is I.m expecting the comparison need to be done for the current set of period i.e April-August till now this year and the same period last year but in my case i think it is taking the entire year and i'm not sure that whether the fiscal logic is working fine thogh?

[v-lgarikapat]

Hi @GanesaMoorthyGM ,

Thanks for reaching out to the Microsoft fabric community forum.

@AllisonKennedy ,

Thanks for your prompt response

@GanesaMoorthyGM , 

I wanted to follow up and confirm whether you’ve had the opportunity to review the information provided by @AllisonKennedy  . If you have any questions or need further clarification, please don’t hesitate to reach out.

We appreciate your collaboration and support!

Best regards,
Lakshmi.

[v-lgarikapat]

Hi @GanesaMoorthyGM ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,
Lakshmi.

[v-lgarikapat]

Hi @GanesaMoorthyGM ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,
Lakshmi.

[AllisonKennedy · Accepted solution]

@GanesaMoorthyGM  Is there a reason you're using so many different functions for the time intelligence? Have you marked your date table as a date table? I always try to use DATEADD function as it's very versatile and then I only need to learn one function: https://www.sqlbi.com/articles/differences-between-dateadd-and-parallelperiod-in-dax/

If you don't have a filter context limiting the current period to 'last x months' or 'on or before today' or similar, then when you shift to 1 year ago, it also won't have that filter. So you'll be comparing the partial year this year with a full year last year. To overcome this you need to add a filter for on or before today, either within your measure or at the report level (depends what you want the graphs to do for forcasting).