cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

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
2 REPLIES 2
Super User

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

Frequent Visitor

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.