Forum Discussion

JoshSHardscape's avatar
JoshSHardscape
Helper I
8 years ago
Solved

Comparing Last Year Sales Values

Hi all,   Been working for the last day or so on producing some projection/tracking reports for sales. I've managed to get most rows of the matrix calculating what I want but am getting stuck when ...
Need Help
  • Floriankx's avatar
    Floriankx
    8 years ago

    Hello,

     

    please provide us the formula how you calculated SumLYLineValue.

     

    Did you use Sameperiodlastyear or DateAdd(DateTable;-1;Year)?

JoshSHardscape's avatar
JoshSHardscape
Helper I
8 years ago

Hi again,

 

I have produced a separate date table and marked it as such, I used the CALENDAR function to product dates from 2014-2023 and related this to my main order data's order date as I understood this is necessary for most time-intellegance functions. When I filter the table below the matrix by actual year the SumLYLineValue seems to work as intended as below:

 

 

It is only when the financial year filter is used that I have problems - this is linked to date and created with the formula: FYear = IF('Sage Data'[Order Date].[MonthNo]<4,CONCATENATE('Sage Data'[Order Date].[Year]-1,CONCATENATE("/",RIGHT('Sage Data'[Order Date].[Year],2))),CONCATENATE('Sage Data'[Order Date].[Year],CONCATENATE("/",RIGHT('Sage Data'[Order Date].[Year],2)+1)))

 

 

 

 

Floriankx's avatar
Floriankx
Solution Sage
8 years ago

Hello,

 

Is FYear a calculated column?

Please try to create FYear in your DateTable instead of your SageData.

  • JoshSHardscape's avatar
    JoshSHardscape
    Helper I
    8 years ago

    I've created FYear in the date table now using FYear = IF(Month('Date'[Date])-3<1,CONCATENATE(YEAR('Date'[Date])-1,CONCATENATE("/",RIGHT(YEAR('Date'[Date]),2))),CONCATENATE(Year('Date'[Date]),CONCATENATE("/",RIGHT(YEAR('Date'[Date]),2)+1))). However I'm not sure if it helps as using this column for the slicer produces the same incorrect results.

    • Floriankx's avatar
      Floriankx
      Solution Sage
      8 years ago

      Do you refer to the new FYear in your slicer?

      • JoshSHardscape's avatar
        JoshSHardscape
        Helper I
        8 years ago

        Yes I used the new column but it didn't seem to make a difference. I was thinking of a different option where I could use something similar to the calculate function to say calculate line value where FY-Month=FYMonth-1. With FY-Month simply being FY and month concatenated and then FYMonth-1 being the equivalent value 1 year before i.e. FY-Month=2015/161 then FYMonth-1=2014/151

         

        I've created the FYMonth-1 column without problems but can't seem to apply it how I want to the calculate function.

         

        Thanks again, I hope I'm explaining things well enough