Excel SUMIFS & COUNTIFS formula to PowerBI DAX conversion

[RETJF]

Hi guys,

Hoping someone could lend a hand converting the following excel formula for PowerBI. 

=IF(D2="LAP",IF(COUNTIFS(G:G,G2,E:E,E2,B:B,B2,A:A,A2,C:C,SUM(C2-1))>0,(SUMIFS(F:F,G:G,G2,E:E,E2,B:B,B2,A:A,A2,C:C,SUM(C2-1))),0),0)

A here's a screenshot of the table with the formula producing the desired result:

The formula in question is in column H.

The outcome to be achieved is:

If a row with 'LAP' in column D is found, it returns the value (column F) from the row that contains the same client (column G), the same service type (column E), the same Month (column B), the same day (column A) but the year (column C) is the previous one to the one in the row with 'LAP'. If it doesn't detect 'LAP' in the given row, it always defaults to 0.

As such, the far right column in the table has just the one row with LAP showing £2000, which is sourced from the value located 4 rows above and 2 columns to the left.

Every Dax formula I have tried has so far has failed to the extent they probably aren't a helpful starting point for troubleshooting. AI converters have also failed just as miserably in tackling this one - proof the machines aren't taking over just yet xD

Any assistance in this would be hugely appreciated! 

Cheers,

RETJF

[Greg_Deckler]

@RETJF You can find equivalent DAX here: Excel to DAX Translation - Microsoft Fabric Community