Is there a way how to calculate and visualize data for missing dates using DAX

[HCK]

Hello,

as a relativelly new to DAX I am in front of for me relativelly big data which I assume would be better to handle on DAX lever rather than Power Query - matching qty for materials (8000+) for each day (365+) and that based on random dated input.
I have a Data table and a Calendar table (Day by Day) linked together with the key day.
Data(Day)*:1Calendar(Day)
I would like to transform the data from:

Data table:

Day	Material	Qty
01.09.2022	A	10
01.09.2022	B	100
01.09.2022	C	1000
08.09.2022	A	20
08.09.2022	B	200
08.09.2022	D	20000
15.09.2022	A	30
15.09.2022	B	3000
15.09.2022	C	3000
15.09.2022	D	30000

To:

Intended result (In Matrix table) 

Day/Qty	A	B	C	D
01.09.2022	10	100	1000	0
02.09.2022	10	100	1000	0
03.09.2022	10	100	1000	0
04.09.2022	10	100	1000	0
05.09.2022	10	100	1000	0
06.09.2022	10	100	1000	0
07.09.2022	10	100	1000	0
08.09.2022	20	200	1000	20000
09.09.2022	20	200	1000	20000
10.09.2022	20	200	1000	20000
11.09.2022	20	200	1000	20000
12.09.2022	20	200	1000	20000
13.09.2022	20	200	1000	20000
14.09.2022	20	200	1000	20000
15.09.2022	30	300	3000	300000
16.09.2022	30	300	3000	300000

The purpose is that the original data for saving purposes shows only data with change and I would like to reconstruct every day status.
I can imagine doing that on Power Query level however I assume that will be too big and don't think it will be the right approach.

I tried to do some measures to do the filling but it all seem to only reflect some dates and not all from the Calendar.

I will appreciate a lot an advice leading to the proper solution. 
Thank you very much for your help  in advance.

HCK

[amitchandak · Accepted solution]

@HCK , First create a new column

End Date = coalesce(Minx(filter(Table, [Material] = earlier([Material]) && [Date] > earlier([Date])),  [Date]), [Date]+1)

then use table of measure way in dax and use a matrix visual

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Table

https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b

[amitchandak · Accepted solution]

@HCK , First create a new column

End Date = coalesce(Minx(filter(Table, [Material] = earlier([Material]) && [Date] > earlier([Date])),  [Date]), [Date]+1)

then use table of measure way in dax and use a matrix visual

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Table

https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b

[HCK · Accepted solution]

@amitchandak Thank you very much. You showed me the way.
I needed to adjust the new column a bit so that it works exactly as intended to:

EndDate = coalesce(Minx(filter(Data, [Material] = earlier([Material]) && [StartDate] > earlier([StartDate])),[StartDate]-1),[StartDate]+1)

I adjusted as well the following measure differently (simplified): 

Val by day of Month = CALCULATE (SUMX(Filter(CROSSJOIN('Data','Date'),'Date'[Date] >= 'Data'[StartDate] && 'Date'[Date]<= 'Data'[EndDate]),Data[Qty]))
It leads to wanted results!
Thank you very much again!