Forum Discussion

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

Last Financial Year Sales

Hi evryone,

 

I'm trying to calculate last financial years revenue for this timeframe 01/07/2023 - 29/08/2023 (today last year)

 

This formla is calculating the entire final year from 01/07/2023 - 30/06/2024

SalesPreviousFY = CALCULATE(SUM(Sales[Gross]), FILTER(FYDateTable, FYDateTable[FinancialYear] = YEAR(TODAY()) - 1))
What do I need to change to make it calculate correctly?
 
 
I careted following dateTable:
FYDateTable = CALENDAR(DATE(2020, 7, 1), DATE(2025, 6, 30))
and added following column
FinancialYear =
IF(MONTH(FYDateTable[Date]) >=7,
Year(FYDateTable[Date]),
Year(FYDateTable[Date]) - 1
)
 
Can someone able to help me out?
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, cocoloco79 

     

    Sorry I can't open your link. But you can try the following.

    Current FY Sum = 
Var _Maxselectdate = MAXX(ALLSELECTED('FYDateTable'[Date]),FYDateTable[Date])
Var _date1=DATE(YEAR(_Maxselectdate),7,1)
Var _date2=DATE(YEAR(_Maxselectdate),MONTH(_Maxselectdate),DAY(_Maxselectdate))
Var _Sum1=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'[Date]),[Date]>=_date1&&[Date]<=_date2))
Var _Sum2=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'[Date]),[Date]<=_date1-1&&[Date]>=_date2))
Return
IF(MONTH(_Maxselectdate)>=7,_Sum1,_Sum2)

    Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

12 Replies

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

    Hi,

    This measure should work

    Measure = calculate(SUM(Sales[Gross]),samperiodlastyear(FYDateTable[Date]))

    Ensure that the slicer is built from the FYDataTable Table.

    Hope this helps.

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

      Hi Ashish,

       

      I have tried your measure: Measure = calculate(SUM(Sales[Gross]),sameperiodlastyear(FYDateTable[Date]))

      However, it returns blank columns.

      The sales table and the Date Table are joined via a many to many relationship. All totals works perfectlt to the cent. Only issue is that I cannot calcuate the same sales data for last years period. 

       

      I have also tried this measure, but totals are not correct.

      SalesPreviousFY = CALCULATE(
    SUM(Sales[Gross]),
    FILTER(
        FYDateTable,
        FYDateTable[FinancialYear] = YEAR(SAMEPERIODLASTYEAR(FYDateTable[Date]))
    )
)

       Any Ideas?

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

        It should be a Many to One and single relationship.  If it still does not work, then share the download link of the PBI file.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, cocoloco79 

     

    You can try the following methods. I simply modeled some data.

    Measure = 
Var _Maxselectdate = MAXX(ALLSELECTED('FYDateTable'[Date]),FYDateTable[Date])
Var _date1=DATE(YEAR(_Maxselectdate)-1,7,1)
Var _date2=DATE(YEAR(_Maxselectdate)-1,MONTH(_Maxselectdate),DAY(_Maxselectdate))
Var _Sum1=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'[Date]),[Date]>=_date1&&[Date]<=_date2))
Var _Sum2=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'[Date]),[Date]<=_date1-1&&[Date]>=_date2))
Return
IF(MONTH(_Maxselectdate)>=7,_Sum1,_Sum2)

    Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

      Hi Anonymous 

       

      Thank you for your help. I tried to apply the logic to the report but failed to do so.

      I see you are calcuating the value from the date, where I need to calaute the revenue from this financial year and then compare it to last years data in the same period. Here is is pbix Last Financial Year Comparison 

       

      Could you please see if you can help me out?

       

      Thank you

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi, cocoloco79 

         

        Sorry I can't open your link. But you can try the following.

        Current FY Sum = 
Var _Maxselectdate = MAXX(ALLSELECTED('FYDateTable'[Date]),FYDateTable[Date])
Var _date1=DATE(YEAR(_Maxselectdate),7,1)
Var _date2=DATE(YEAR(_Maxselectdate),MONTH(_Maxselectdate),DAY(_Maxselectdate))
Var _Sum1=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'[Date]),[Date]>=_date1&&[Date]<=_date2))
Var _Sum2=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'[Date]),[Date]<=_date1-1&&[Date]>=_date2))
Return
IF(MONTH(_Maxselectdate)>=7,_Sum1,_Sum2)

        Please see the attached document.

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.