Distribute Value to ratio and date offset

[PD_FKL]

Hello together,

I have a table1 containing Value and DueDate.

Additionally I have a table2 which gives me some offsets and ratios e.g.

OffsetDays	Ratio
0	60%
20	20%
40	20%

So I would like to distribute my value to 60% on the due date 20% 20 days after the due date and another 20% 40 days after the due date.

Since I am not using Power BI but Excel I can't create a calculated table and since I want to apply this logic to multiple tables it would be great if this could be solved with a DAX measure instead of merging and expanding in M.

I hope someone has a suitable solution.

Best regards

Florian

[PD_FKL]

Thanks for pointing me in the right direction.

I had to convert it to a Crossjoin since Excel was giving me a context error, in Power BI it works smoothly.

Phasing2:=VAR a = 
//https://dax.guide/summarizecolumns/
Filter(
	ADDCOLUMNS(
		CROSSJOIN(
			VALUES( Calendar[Date] );
			Dates; 
			Facts
		);
		"Amount"; If( [Date] = [DueDate] + [Offset]; [Value] * [Ratio] )
	);
	NOT( ISBLANK( [Amount] ) )
)
//SUMMARIZECOLUMNS( Calendar[Date];Facts[DueDate];Facts[Value];Dates[Offset];Dates[Ratio])
//VAR b= ADDCOLUMNS( a; "amt"; IF(Calendar[Date] = Facts[DueDate] + [Offset]; [Value] * [Ratio]))
RETURN
SUMX( a; [Amount])

[lbendlin]

Yes, my use of SUMMARIZECOLUMNS was a bit, uhm, lazy.  Could have done a cross join from the get-go. Same idea.

[lbendlin · Accepted solution]

That turned out to be surprisingly simple.