Show YTD when category doesn't have values for the selected date

[RaulB]

I have the next matrix with scrap type and stations and YTD and a single select slicer for the date. 

When I choose the date I should see the YTD until the selected date, but if I don't have any value for Scrap or station in the selected date it shows 0 when it should show the YTD until that day. Here is the formula:

YTD =

CALCULATE(

    divide(

        Sum(

            'Scrap'[Scrap_Cost]

        ),

        sum(

            Cost[day_Cost]

        )

    )+0,

    DATESYTD(

        'Calendar'[Date]

    )

)

Here is the expected output and sample tables:

Cost table

Date	day_Cost
8/6/2023 0:00	1751597
8/5/2023 0:00	1153887
8/4/2023 0:00	1690295
8/3/2023 0:00	1480194
8/2/2023 0:00	1532197
8/1/2023 0:00	1290290

Scrap table

ScrapType	BookingArea	Date	Scrap_Cost
Scrap II	Build	8/6/2023 0:00	16348
Scrap II	CON Station	8/6/2023 0:00	4714
Scrap II	LVK Station	8/6/2023 0:00	23395
Scrap II	RM Station	8/6/2023 0:00	945
Scrap II	SEA Station	8/6/2023 0:00	21341
Scrap III	AG Station	8/6/2023 0:00	29606
Scrap IV	AG	8/6/2023 0:00	17114
Scrap IV	ARY	8/6/2023 0:00	22933
Scrap IV	LRA	8/6/2023 0:00	12294
Scrap IV	LRATT	8/6/2023 0:00	28622
Scrap IV	PLK	8/6/2023 0:00	27536
Scrap II	Build	8/5/2023 0:00	29116
Scrap II	CON Station	8/5/2023 0:00	9183
Scrap II	LVK Station	8/5/2023 0:00	6852
Scrap II	RM Station	8/5/2023 0:00	22300
Scrap II	SEA Station	8/5/2023 0:00	25686
Scrap III	AG Station	8/5/2023 0:00	21714
Scrap IV	AG	8/5/2023 0:00	23017
Scrap IV	ARY	8/5/2023 0:00	26533
Scrap IV	LRA	8/5/2023 0:00	27034
Scrap IV	LRATT	8/5/2023 0:00	7410
Scrap IV	PLK	8/5/2023 0:00	8622
Scrap II	Build	8/4/2023 0:00	15481
Scrap II	CON Station	8/4/2023 0:00	23777
Scrap II	IALR StationB	8/4/2023 0:00	13958
Scrap II	LVK Station	8/4/2023 0:00	15435
Scrap II	Sanolc Station	8/4/2023 0:00	11679
Scrap II	SEA Station	8/4/2023 0:00	2996
Scrap III	AG Station	8/4/2023 0:00	11205
Scrap IV	AG	8/4/2023 0:00	24462
Scrap IV	ARY	8/4/2023 0:00	13699
Scrap IV	LRA	8/4/2023 0:00	10115
Scrap IV	LRATT	8/4/2023 0:00	10510
Scrap IV	PLK	8/4/2023 0:00	18054
Scrap I	Aerf Station	8/3/2023 0:00	11144
Scrap II	Build	8/3/2023 0:00	27665
Scrap II	CON Station	8/3/2023 0:00	20027
Scrap II	IALR Station_Compound	8/3/2023 0:00	13262
Scrap II	IALR StationB	8/3/2023 0:00	2206
Scrap II	LVK Station	8/3/2023 0:00	7412
Scrap II	Sanolc Station	8/3/2023 0:00	14233
Scrap II	SEA Station	8/3/2023 0:00	29416
Scrap III	AG Station	8/3/2023 0:00	7694
Scrap IV		8/3/2023 0:00	13414
Scrap IV	AG	8/3/2023 0:00	942
Scrap IV	ARY	8/3/2023 0:00	23843
Scrap IV	LRA	8/3/2023 0:00	27939
Scrap IV	LRATT	8/3/2023 0:00	9346
Scrap IV	PLK	8/3/2023 0:00	8909
Scrap I	Aerf Station	8/2/2023 0:00	7707
Scrap II	Build	8/2/2023 0:00	27759
Scrap II	CON Station	8/2/2023 0:00	9761
Scrap II	IALR StationB	8/2/2023 0:00	4608
Scrap II	LVK Station	8/2/2023 0:00	20418
Scrap II	RM Station	8/2/2023 0:00	21033
Scrap II	Sanolc Station	8/2/2023 0:00	11488
Scrap II	SEA Station	8/2/2023 0:00	25013
Scrap III	AG Station	8/2/2023 0:00	26575
Scrap IV	AG	8/2/2023 0:00	25686
Scrap IV	ARY	8/2/2023 0:00	16010
Scrap IV	LRA	8/2/2023 0:00	26136
Scrap IV	LRATT	8/2/2023 0:00	9983
Scrap IV	PLK	8/2/2023 0:00	25224
Scrap I	Aerf Station	8/1/2023 0:00	3555
Scrap II	Build	8/1/2023 0:00	29792
Scrap II	CON Station	8/1/2023 0:00	24425
Scrap II	IALR Station_Compound	8/1/2023 0:00	21692
Scrap II	IALR StationB	8/1/2023 0:00	24237
Scrap II	LVK Station	8/1/2023 0:00	12970
Scrap II	RM Station	8/1/2023 0:00	9584
Scrap II	Sanolc Station	8/1/2023 0:00	2031
Scrap II	SEA Station	8/1/2023 0:00	9789
Scrap III	AG Station	8/1/2023 0:00	17708
Scrap IV	AG	8/1/2023 0:00	1433
Scrap IV	ARY	8/1/2023 0:00	27768
Scrap IV	LRA	8/1/2023 0:00	7986
Scrap IV	LRATT	8/1/2023 0:00	29335
Scrap IV	MIX	8/1/2023 0:00	2766
Scrap IV	PLK	8/1/2023 0:00	6250

[v-yanjiang-msft · Accepted solution]

Hi @RaulB ,

According to your description, I create a sample by the data you provided, but it can return a correct value.

I attach my sample below for your reference. If you need more help, please feel free to contact me.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RaulB]

Thank you

[Mahesh0016]

@RaulB is ok, but I can't Visualization how you EndOutPut Display.

[Mahesh0016]

Hey @RaulB, Please can you share your Expected output?

[RaulB]

Scrap I should be 0.71% because that's what it is in the previous day, but it is 0.00%