Forum Discussion

PD_FKL's avatar
PD_FKL
New Member
2 years ago
Solved

Distribute Value to ratio and date offset

Hello together,

 

I have a table1 containing Value and DueDate.

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

OffsetDaysRatio
060%
2020%
4020%

 

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

DAX
Desktop
Excel
TimeIntelligence

3 Replies

  • PD_FKL's avatar
    PD_FKL
    New Member
    2 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

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