topic Re: Distribute Value to ratio and date offset in DAX Commands and Tips

Distribute Value to ratio and date offset

PD_FKL — Wed, 14 Feb 2024 20:31:48 GMT

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

Re: Distribute Value to ratio and date offset

lbendlin — Thu, 15 Feb 2024 02:29:26 GMT

That turned out to be surprisingly simple.

Re: Distribute Value to ratio and date offset

PD_FKL — Thu, 15 Feb 2024 12:57:02 GMT

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])

Re: Distribute Value to ratio and date offset

lbendlin — Thu, 15 Feb 2024 13:34:28 GMT

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