Tracking First Year of Sales

[analytics_uk]

Hi guys

I have had a very specific request from the business around tracking the first year of sales. Our staff are bonused on this but they are bonused quarterly. So I need to take sales from the date of product launch for 12 calendar months and then to the end of that quarter. For example, if a product was launched on Feburary 7th 2019, we need to track until 31st March 2020. The ENDOFQUARTER measure wasn't working properly and was just giving me the 7th of the next month so I have imported a column into the master date file with the end of tracking date for each date. However no formula I use is letting me select that field. I have tried to pull it into a calculated column into my product table but it won't find the column at all. 

Any ideas how you would do this? 

I effectively need to create a matrix like this:

		Q4	Q4	Q1	Q1	Q1	Q2
		2019	2019	2020	2020	2020	2020
	Product Launch	November	December	January	February	March	April
Product 1	07/10/2018	£100	£200
Product 2	01/01/2019	£300	£250	£400	£450	£200
Product 3	07/02/2020			£0	£200	£300	£300

The product will continue to generate revenue after the date I want to stop tracking, but we track Y1 of sales of certain products particularly. 

Thank you for any help!

[v-kelly-msft · Accepted solution]

Hi @analytics_uk ,

I make a sample table for example:

First create a calendar table using below dax expression:

Table 2 = CALENDAR(DATE(2018,1,1),DATE(2021,12,31))

Then create 2 calculated column as below to get the Quarter  and endofquarter:

Table 2 = DATEADD(ENDOFQUARTER('Table 2'[Date]),1,YEAR)

Column 2 = "Q"&QUARTER('Table 2'[Endofquarter])

Finally create a measure as below:

Measure = IF(MAX('Table'[Product Launch]) IN FILTERS('Table 2'[Date]),
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Product Launch]<='Table'[_Endofquarter]&&'Table'[_endofyear]=MAX('Table'[_endofyear])&&'Table'[Quarter]=MAX('Table'[Quarter]))),BLANK())

And you will see:

Here is the related .pbix file.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[analytics_uk]

Thank you Kelly!! 

[amitchandak]

@analytics_uk ,

Create a new column in product

end_date = eomonth(dateadd(product[start_date],12,month),0)

Try a measure

Sales = CALCULATE(sumx(FILTER(sales,(Sales[Date]) >= min(Product[Start Date]) && (Sales[Date]) <= max(Product[End Date]) ),(Sales[sales])))

[mahoneypat]

Here is what a measure that does this might look like.  See the comments on how you could modify it to meet your needs.  The enddate variable has the part you probably need to get 12 mos from end of the quarter.

FirstYearSales Through Quarter = var launchdate = SELECTEDVALUE(Launch[Launch]) // set this variable to whatever expression get you the launch date for the product in context

var enddate = EOMONTH(launchdate,MOD(3-MONTH(launchdate),3)+12) // this variable figures out the end of quarter + 12 mos

return CALCULATE([Sales Total], DATESBETWEEN('Date'[Date], launchdate, enddate)) //put your measure in place of [Sales Total] to calculate it over the needed date range

If this works for you, please mark it as the solution.  Kudos are great too.  Please let me know if it doesn't or if any questions.

Regards,

Pat