Forum Discussion

GanesaMoorthyGM's avatar
GanesaMoorthyGM
Helper II
1 year ago
Solved

YOY Growth Comparison tips

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?


Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • AllisonKennedy's avatar
    AllisonKennedy
    1 year ago

    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).

4 Replies

  • v-lgarikapat's avatar
    v-lgarikapat
    Community Support
    11 months ago

    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's avatar
      v-lgarikapat
      Community Support
      11 months ago

      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's avatar
        v-lgarikapat
        Community Support
        11 months ago

        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's avatar
    AllisonKennedy
    Community Champion
    1 year ago

    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).