Forum Discussion
YTD including only unique values
Solution SagePlease 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?