Announcements
cancel
Showing results for
Did you mean:
Creator III

## Last Year Sales Comparison Based On The Given Date

Hi Experts,

I have created the Current Year's and Last Year's sales reports separately. I need to show the last year's sales based on the given date.

I am maintaining the separate source file which is given below.

 Working Day CY DateKey LY DateKey 1 20230301 20220301 2 20230302 20220302 3 20230303 20220303 4 20230304 20220304 5 20230305 20220304 6 20230306 20220305 7 20230307 20220307 8 20230308 20220308 9 20230309 20220309 10 20230310 20220310 11 20230311 20220310 12 20230312 20220311 13 20230313 20220311 14 20230314 20220312 15 20230315 20220314 16 20230316 20220315 17 20230317 20220316 18 20230318 20220317 19 20230319 20220318 20 20230320 20220318 21 20230321 20220319 22 20230322 20220321 23 20230323 20220323 24 20230324 20220324 25 20230325 20220325 26 20230326 20220325 27 20230327 20220326 28 20230328 20220328 29 20230329 20220329 30 20230330 20220330 31 20230331 20220331

If we are in 20230310, we need to show  till 20220310 sales whereas we are in 20230313, we need to show till 20220311 sales. We need to show last year sales based on the given date table.

Sum({<Year = {\$(=max(Year)-1)},Date_Key = {"<=\$(=Date(AddYears(Date#(Max(Date_Key),'YYYYMMDD'), -1), 'YYYYMMDD'))"}>}Sales_Value)

I have Date table in my data model and sales table has a date key column.

Labels (5)

• ### SaaS

16 Replies
Creator

Hi,

set a variable MaxYear=max(Year). Then go to the Expressions Tab and in definition write =Sum(Year=MaxYear,Sales_Value). Afterwards write one more expression =Sum(Year=MaxYear-1,Sales_Value).

In this way you will have the comparison between the years. If you prefer this analysis to compare months or Quarters CY with LY then you have to have connect your table with a calendar.

Thanks

MVP

Hi

What is the link between Date tables and workingday, CY DateKey & LY DateKey info?

Thanks & Regards, Mayil Vahanan R

The mistake is the use of the date_key as dimension because only one date belonged to it - and not the one from the previous year or any other date. Principally it's possible to force any calculation to each dimension-value but it's difficult, slow and has a lot of disadvantages - it's very seldom expedient.

Better would be you removes the year-information from the dimension - just using MM-DD which comes from your master-calendar and is linked to your date_key. Also useful would be to create all kinds of flags of YTD and MTD of any years within the calendar and/or if they don't mandatory relate to today() else to selected values with the UI simplifying logic like:

daynumberofyear(date) as ContinuousDay,
year(date) - year(today()) as YearOffset

and the the expressions may look like:

sum({< ContinuousDay = {"<=\$(=max(ContinuousDay))"}, YearOffset = {"\$(=max(YearOffset ))"}>} Value)
sum({< ContinuousDay = {"<=\$(=max(ContinuousDay))"}, YearOffset = {"\$(=max(YearOffset )-1)"}>} Value)

Creator III
Author

Hi,

The CY Datekey is the key of the Dates table and this excel files.

Creator III
Author

Hi Marcus,

I have joined the master calendar and this Excel by Date_Key. I have created the two fields which are you mentioned. The values are not populating for LY.

Kindly help me out with this task.

I have used the below expression:

Sum({<ContinuousDay = {"<=\$(=max(ContinuousDay))"}, YearOffset = {"\$(=max(YearOffset ))"}>}Sales_Value)

Sum({<,ContinuousDay = {"<=\$(=max(ContinuousDay))"}, YearOffset = {"\$(=max(YearOffset )-1)"}>}Sales_Value)

Thanks in advance @marcus_sommer @MayilVahanan @sunny_talwar

If you have not only a single selection on the day else various other ones to the period-information you will need to address them, too within the condition - means ignoring them, maybe like:

Sum({< ContinuousDay = {"<=\$(=max(ContinuousDay))"},
YearOffset = {"\$(=max(YearOffset )-1)"},
Year, Month >}Sales_Value)

Beside this there is a small syntax issue in your expression - a leading comma before ContinuousDay - probably a copy & paste mistake.

Creator III
Author

Hi Marcus,

If i select Date_Key 20230314, LY sales should be adding upto 20220312 but it is added upto 20220314. Same if we select 20230313, LY sales should be adding upto 20220311.

 Working Day CY DateKey LY DateKey 1 20230301 20220301 2 20230302 20220302 3 20230303 20220303 4 20230304 20220304 5 20230305 20220304 6 20230306 20220305 7 20230307 20220307 8 20230308 20220308 9 20230309 20220309 10 20230310 20220310 11 20230311 20220310 12 20230312 20220311 13 20230313 20220311 14 20230314 20220312 15 20230315 20220314 16 20230316 20220315 17 20230317 20220316 18 20230318 20220317 19 20230319 20220318 20 20230320 20220318 21 20230321 20220319 22 20230322 20220321 23 20230323 20220323 24 20230324 20220324 25 20230325 20220325 26 20230326 20220325 27 20230327 20220326 28 20230328 20220328 29 20230329 20220329 30 20230330 20220330 31 20230331 20220331

Based on the CY DateKey selection, LY sales should be adding upto the given LY DateKey.

Thanks in advance @marcus_sommer @MayilVahanan @sunny_talwar

Like above hinted I wouldn't try to use multiple date-keys - neither for this task nor another one - else just one key which is the link between the fact-table and the master-calendar.

If you don't want to refer the YTD logic to calendar-days else to working-days you could remain by the above suggested main-logic just by adjusting the creation of the ContinuousDay. This might be done by using an appropriate offset-value - if I remember it correctly it's just +- 1 between the years - or by creating a working-day flag of 0/1 (in regard to weekends, holidays and so on) which are within a following step accumulated (for example by using interrecord-functions like previous() and peek() within a preceding-load of the calendar).

Creator III
Author

Hi Marcus,

Is it possible to bring the LY DateKey from 20220301 to 20220312 when I select 20230314 from Date_Key.

I am using the below expression with possible of LY DateKey.

Sum({<Version_Desc = {'Actual','Adjustment'},ContinuousDay = {"<=\$(=max(ContinuousDay))"}, YearOffset = {"\$(=max(YearOffset )-1)"},Year,Month,Day_of_Week,Date_Key=p([LY DateKey])>}Sales_Value)/1000

Thanks in advance @marcus_sommer @MayilVahanan @sunny_talwar