Forum Discussion

cocoloco79's avatar
cocoloco79
Icon for Helper III rankHelper III
2 years ago
Solved

Financial Year, Current vs Last

Hi Everyone,

 

I know it's been discussed here but I'm still stuck.

Need to create a report that will compare this year's Gross Sales by Product  (01/07/24 - today) vs last year (01/07/23 - 30/06/24) Gross Sale by product.

 

I created a Date table: 

DateTable = CALENDAR(DATE(2020, 7, 1), DATE(2025, 6, 30))
Then created a column for each Financial year:
FinancialYear = IF(MONTH([Date]) >= 7, YEAR([Date]), YEAR([Date]) - 1)
 
I createted a many to one relationship with the SalesDataTable and the DateTable.
 
From here, I created two measures, one for Current FY and the other for previous FY.
SalesCurrentFY = CALCULATE(SUM(Grower[Gross]), FILTER(DateTable, DateTable[FinancialYear] = YEAR(TODAY())))
SalesPreviousFY = CALCULATE(SUM(Grower[Gross]), FILTER(DateTable, DateTable[FinancialYear] = YEAR(TODAY()) - 1))
 
The result is missing a heap of dates and therefore the Gross Sale for each FY is incorrect.

Can someone help me understand what I'm missing here?

 

 
 
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi cocoloco79 - Ok , can you please try below 

     

    SalesCurrentFY =
    CALCULATE(
    SUM(Grower[Gross]),
    DATESBETWEEN(
    DateTable[Date],
    DATE(YEAR(TODAY()), 6, 30), // Start on June 30th instead of July 1st
    TODAY() + 1 // Include today and the next day, just to catch everything
    )
    )

     

    Previous FY , Create below measure

    SalesPreviousFY =
    CALCULATE(
    SUM(Grower[Gross]),
    DATESBETWEEN(
    DateTable[Date],
    DATE(YEAR(TODAY()) - 1, 6, 30), // Start on June 30th instead of July 1st
    DATE(YEAR(TODAY()), 6, 30) + 1 // Extend by one day
    )
    )

    Make sure there are no unintended relationships in your data model that could be affecting the totals.it may help to temporarily remove all filters and relationships.

    Hope this works

     

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi cocoloco79 - Create a new calculated column for FY and here i am considering FY from july 1st to current yr.

    Create two measures to current and PY financial year and match that the date range is correctly captured.

    Current Financial Year Sales
    SalesCurrentFY =
    CALCULATE(
    SUM(Grower[Gross]),
    FILTER(
    ALL(DateTable),
    DateTable[Date] >= DATE(YEAR(TODAY()), 7, 1) &&
    DateTable[Date] <= TODAY()
    )
    )

     

    Another measure for Previous Financial Year Sales
    SalesPreviousFY =
    CALCULATE(
    SUM(Grower[Gross]),
    FILTER(
    ALL(DateTable),
    DateTable[Date] >= DATE(YEAR(TODAY()) - 1, 7, 1) &&
    DateTable[Date] <= DATE(YEAR(TODAY()), 6, 30)
    )
    )

     

    Hope this works.

     

    • cocoloco79's avatar
      cocoloco79
      Icon for Helper III rankHelper III
      2 years ago

      rajendraongole1 Thank you, but I get the same result as in Gross Sales which is about 25% short.

      The data table alligns the correct Financial year, so I belive that working correctly. Its the FY Gross measure that is the issue I think. Anything else I can try?

      • rajendraongole1's avatar
        rajendraongole1
        Icon for Super User rankSuper User
        2 years ago

        Hi cocoloco79 - Ok , can you please try below 

         

        SalesCurrentFY =
        CALCULATE(
        SUM(Grower[Gross]),
        DATESBETWEEN(
        DateTable[Date],
        DATE(YEAR(TODAY()), 6, 30), // Start on June 30th instead of July 1st
        TODAY() + 1 // Include today and the next day, just to catch everything
        )
        )

         

        Previous FY , Create below measure

        SalesPreviousFY =
        CALCULATE(
        SUM(Grower[Gross]),
        DATESBETWEEN(
        DateTable[Date],
        DATE(YEAR(TODAY()) - 1, 6, 30), // Start on June 30th instead of July 1st
        DATE(YEAR(TODAY()), 6, 30) + 1 // Extend by one day
        )
        )

        Make sure there are no unintended relationships in your data model that could be affecting the totals.it may help to temporarily remove all filters and relationships.

        Hope this works