Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jouni900
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?

 

ProductDateMonthSalesFCST
A1.3.2023Mar35
A15.4.2023Apr28
A9.5.2023May19
A10.5.2023May89
A11.5.2023May49
2 REPLIES 2
Kishore_KVN
Super User
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:

Kishore_KVN_0-1683650344839.png

Then create YTD Measure:

YTD = 
CALCULATE(SUM('Table'[Unique Values]),DATESYTD('Table'[Date]))

Output in the report looks like this:

Kishore_KVN_1-1683650406320.png

 

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

 

YearProductCustomerDateMonthSalesNPD FCSTIs NPDCampaign FCSTIs in campaign
2023AX1.3.2023Mar35Yes0No
2023AY15.4.2023Apr28Yes0No
2023AZ9.5.2023May19Yes0No
2023AZ10.5.2023May89Yes0No
2023AZ11.5.2023May49Yes0No
2023BX1.3.2023Mar30No3Yes
2023BY15.4.2023Apr20No5Yes
2023BZ9.5.2023May10No5Yes
2023CX10.5.2023May80No8No
2023CY11.5.2023May40No0No
2023CZ15.4.2023Apr30No0No
2022DX1.3.2022Mar40No0No
2022DY15.4.2022Apr50No0No

 

Are you able to say what might have caused this? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.