Comparing Last Year Sales Values

[JoshSHardscape]

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 it comes to comparing with last year values. I'm not sure if the problem is due to me using fiscal years and matching data up based on that. I'll post a picture of the matrix in its current state and if any particular column/measure is needed to help with the problem let me know!

[Floriankx · Accepted solution]

Hello,

please provide us the formula how you calculated SumLYLineValue.

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

[JoshSHardscape]

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]

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]

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]

Hello,

Is FYear a calculated column?

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

[JoshSHardscape]

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]

Do you refer to the new FYear in your slicer?

[JoshSHardscape]

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

[Floriankx]

Hello,

can you make a Screenshot how it looks if you have Year in your column but FYear as slicer selected 2017?

Best regards.

[JoshSHardscape]

See below screenshot with this filtering, hopefully that's as you wanted!

[Floriankx]

So just to be sure.

Year, Month and FYear are all in your DateTable.

SumLYLineValue:=Calculate(SumLineValue;Sameperiodlastyear(DateTable[Date]))

The Date columns are related and your DateTable is contigous.

Your raw data isn't limited. For example data for Fiscal Year 2016/2017 are filtered or similar?

Did you select different periods?

[JoshSHardscape]

Yes all that is correct, I'll try starting fresh with just the data as it's getting a bit messy with the amount of relationships floating arond now. Hopefully the clean slate helps!

[Floriankx]

Can you remove months so just to compare the years?

[JoshSHardscape]

Hi,

I've ended up starting again and doing some of the links from scratch, this has resulted in me actually ending up with the matrix successfully showing the last year value as intended! Unfortunately it now seems that the target part isn't linking into the matrix as intended. I won't trouble with more questions on it as I feel I am making progress  but thanks so much for your help getting through the original issue!