Forum Discussion
YTD including only unique values
Hi,
I have a following goal in which I am seeking a solution
I have sales data on a product-day level from which the data is aggregated to monthly level. I have campaign and new product sales for which, I have sales data and a monthly level FCST What I would like to track is two things: first, how the volume compares on a monthly level against the monthly target which I am able to do already. However the second point in which I am looking a solution is following the same on a YTD level. I am able to get the YTD sales correct but not YTD FCST.
- SKU NPD FCST Use = SUMX(VALUES('SKU Daily Data 2021-2022 - ÄLÄ'[SKU]),SUMX(VALUES('SKU Daily Data 2021-2022 - ÄLÄ'[Customer]),[SKU NPD FCST]))
- Where, SKU NPD FCST = CALCULATE(AVERAGE('SKU Daily Data 2021-2022 - ÄLÄ'[NPD FCST]),FILTER('SKU Daily Data 2021-2022 - ÄLÄ','SKU Daily Data 2021-2022 - ÄLÄ'[Is a NPD]="Yes"))
Using the example data below, how would I be able to get YTD FCST such that it would sum the unique values only once so if I would look Mar-May it would show YTD 22 as a YTD target?
| Product | Date | Month | Sales | FCST |
| A | 1.3.2023 | Mar | 3 | 5 |
| A | 15.4.2023 | Apr | 2 | 8 |
| A | 9.5.2023 | May | 1 | 9 |
| A | 10.5.2023 | May | 8 | 9 |
| A | 11.5.2023 | May | 4 | 9 |
2 Replies
Please create one column and a measure.
Column DAX will be as below:
Unique Values = Var MonthCount = CALCULATE(COUNT('Table'[Month]),ALLEXCEPT('Table','Table'[Month])) Return IF(MonthCount=1,'Table'[FCST],'Table'[FCST]/MonthCount)Output looks like this:
Then create YTD Measure:
YTD = CALCULATE(SUM('Table'[Unique Values]),DATESYTD('Table'[Date]))Output in the report looks like this:
Jouni900 If this post helps, then please consider accepting it as the solution to help other members find it more quickly.THANK YOU!!
Hi, Kishore_KVN
Thanks for the help. However, I was not able to get the desired outcome and I am sharing the data that I have more in case I did not disclose all necessary information in my first message. Below is the output from the first formula where I have data on one customer. The calendar day column has a month in Finnish so never mind on that 🙂 The YTD measure produced only 0,0. Because the unique values seem so small, I am thinking is it using a larger number of rows (month count) to divide the NPD FCST (0,636) than it should?
Unique Values
NPD FCST
SKU
Calendar Day
Year
Month
5,1807031888798E-05
0,6336
4263659
tiistai 4. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
torstai 27. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
keskiviikko 26. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
tiistai 25. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
perjantai 21. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
torstai 13. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
keskiviikko 12. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
tiistai 4. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
torstai 27. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
keskiviikko 26. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
perjantai 21. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
torstai 13. huhtikuuta 2023
2023
Apr
5,1807031888798E-05
0,6336
4263659
keskiviikko 12. huhtikuuta 2023
2023
Apr
In general I have data on three types of products from 2021-2023 on a daily level. First type is normal products which are not in campaign and are not new products. The second type is new products and the final type of products is campaign products. I have a column which marks if a product is new product (npd) or campaign product and this is determined if the respective FCST column has a value other than 0. Furthermore, I have the data on a customer level, so I have different forecast for different customers.
Year Product Customer Date Month Sales NPD FCST Is NPD Campaign FCST Is in campaign 2023 A X 1.3.2023 Mar 3 5 Yes 0 No 2023 A Y 15.4.2023 Apr 2 8 Yes 0 No 2023 A Z 9.5.2023 May 1 9 Yes 0 No 2023 A Z 10.5.2023 May 8 9 Yes 0 No 2023 A Z 11.5.2023 May 4 9 Yes 0 No 2023 B X 1.3.2023 Mar 3 0 No 3 Yes 2023 B Y 15.4.2023 Apr 2 0 No 5 Yes 2023 B Z 9.5.2023 May 1 0 No 5 Yes 2023 C X 10.5.2023 May 8 0 No 8 No 2023 C Y 11.5.2023 May 4 0 No 0 No 2023 C Z 15.4.2023 Apr 3 0 No 0 No 2022 D X 1.3.2022 Mar 4 0 No 0 No 2022 D Y 15.4.2022 Apr 5 0 No 0 No Are you able to say what might have caused this?
