YTD including only unique values

[Jouni900]

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.

For the monthly level target my measure looks the following for new product (NPD) sales tracking :

• 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

[Kishore_KVN]

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!!

[Jouni900]

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?