Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@RETJF You can find equivalent DAX here: Excel to DAX Translation - Microsoft Fabric Community
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
103 | |
101 | |
84 | |
77 | |
66 |
User | Count |
---|---|
135 | |
109 | |
104 | |
83 | |
73 |