Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
Thank you Kelly!!
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])))
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.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |