Converting excel formula to DAX

[ncguy55]

Hi - I am working on converting an excel document into Power BI. I am struggling to figure out how to convert this formula to DAX:

=MIN(SUMIF(Paydowns[Facility Number],[@[Facility Number]],Paydowns[Transaction Value])-SUMIF(OFFSET(Schedule[[#Headers],[Facility Number]],0,0,ROW([@[Facility Number]])-ROW(Schedule[[#Headers],[Facility Number]])),[@[Facility Number]],OFFSET(Schedule[[#Headers],[Payment Amount]],0,0,ROW([@[Facility Number]])-ROW(Schedule[[#Headers],[Facility Number]]))),[@[Advance Amount]])

Thanks in advance for the help!

 

[RoryA]

It is pretty much always a mistake to try and directly convert an Excel formula to DAX since the underlying structures and output visuals are quite different.

What are you actually trying to calculate?

 

[ncguy55]

Understand, the offset portion of this formula is what I can't figure out. It Sums the payment amounts above the current cell if the facility number matches the current row.

Facility Number	Payment Amount	Offset
1	200,204	-
1	17,796	200,204
1	67,847	218,000
2	68,184	-
2	131,122	68,184
2	91,786	199,306
2	65,561	291,092

Offset Portion:
SUMIF(OFFSET(Schedule[[#Headers],[Facility Number]],0,0,ROW([@[Facility Number]])-ROW(Schedule[[#Headers],[Facility Number]])),[@[Facility Number]],OFFSET(Schedule[[#Headers],[Payment Amount]],0,0,ROW([@[Facility Number]])-ROW(Schedule[[#Headers],[Facility Number]]))

 

[RoryA]

That's basically a running total, which you would typically do as a measure not a calculated column, but that could be tricky either way given that you don't appear to have anything that would indicate an order (row numbers aren't really a thing). If you use PQ to load the data into the data model, you could add an index column, and then you can create a measure that sums all values for the current facility where the index is less than the current index value.

 

[ncguy55]

Yes, I could add the index number in PQ. I will try to see if I can figure the measure out. Thanks for your help!