Forum Discussion

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

Floriankx
8 years ago
Hello,

please provide us the formula how you calculated SumLYLineValue.

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

Solution Sage

8 years ago

Hello,

please provide us the formula how you calculated SumLYLineValue.

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

Helper I

8 years ago

Hi, thanks for the response.

This measure was calculated using the following:

SumLineValue = SUM('Sage Data'[Line Value])

SumLYLineValue = CALCULATE([SumLineValue],SAMEPERIODLASTYEAR('Sage Data'[Order Date].[Date]))

Would the DateAdd command be more appropriate or is it functionally identical?

Thanks,

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
  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
    Solution Sage
    8 years ago
    Hello,
    
    Is FYear a calculated column?
    Please try to create FYear in your DateTable instead of your SageData.

Related content