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

Floriankx's avatar
Floriankx
Solution Sage
8 years ago

Hello,

 

the functions are similar.

DateAdd is great if you want to move back or forward day or months. In your case Sameperiodlastyear is great.

Do you have a contigous date table?

In such cases it's usually the best to have a separate date table which you relate to your date colum.

Then you calculate with Sameperiodlastyear(DateTable[Date]) and you put the Date data to your Pivot.

 

I'm not sure but this may cause your trouble.

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?