Forum Discussion
Translate Excel Formula DAX
Hi,
I would like to translate the following formula into DAX. Is this possible? Referring to above row, but this could also be referring to the lower "SSL Week number".
4 Replies
Anonymous , Can you share sample data and sample output in table format? With formula.
In case you looking week vs week, refer
Thanks for your quick reply, don't know how to upload an attachment though... below a sample set with the formula for column G
Formula for column G batch consumption
=IF(B2=B1,D2-D1,D2)+IF(AND(B2=B1,F1=0),G1-E1,0)
Desc Material SSL Week FORECAST STOCK scrap_volume Batch consumption CARTE D'OR CREME DOLCE 500ML 12X 18696601 (blank) (blank) 0 0 #VALUE! RAMA DCA 500ML 12X 67818277 40.2020 696 524 0 696 BB CCA15 8X1L CT EB FR/NL 67876562 42.2020 20615 217 0 20615 SOLO MP31 8X1L CT EB BE 67876570 40.2020 6477 0 0 6477 SOLO MP31 8X1L CT EB BE 67876570 43.2020 8305 331 0 8305 SOLO CCA15 8X1L CT EB BE 67876572 42.2020 18223 1455 0 18223 SOLO CF CCA15 12X250ML BOT EB BE 67899032 34.2020 2146 1081 0 2146 FLORA CCA15 ND 1L 8X 67946284 40.2020 153 180 27 153 FLORA MP35 ND 8X1L BRI EB DE NL BE 67946299 37.2020 1050 416 0 1050 ZEEUWS MEISJE WIKKEL 500G 20X 67607878 34.2020 3926 1128 0 3926 ZEEUWS MEISJE WIKKEL 500G 20X 67607878 37.2020 4606 999 0 3478 ZEEUWS MEISJE WIKKEL 500G 20X 67607878 39.2020 4606 3016 537 2479 ZEEUWS MEISJE MARGARINE 250G 20X 67615138 32.2020 18277 14872 0 18277 BERTOLLI MEL MLD BUT OO 8X225G TBT EB NL 67722517 33.2020 9089 2108 0 9089 BERTOLLI MEL MLD BUT OO 8X225G TBT EB NL 67722517 36.2020 14761 4860 0 12653 BECEL GOLD MARG 8X250G TRT EB BE 67722547 33.2020 2129 67 0 2129 BECEL GOLD MARG 8X250G TRT EB BE 67722547 37.2020 3827 1993 0 3760 BERTOLLI WIKKEL ONGEZOUTEN 250G 20X 67727243 32.2020 4944 519 0 4944 BECEL LFM LIGHT 24X250G TRT EB BE 67729013 29.2020 1318 0 0 1318 BECEL LFM LIGHT 24X250G TRT EB BE 67729013 35.2020 10148 4356 0 10148 BECEL LFM LIGHT 8X250G TRT EB BE 67729022 31.2020 2263 0 0 2263 BECEL LFM LIGHT 8X250G TRT EB BE 67729022 33.2020 4097 1510 0 4097 BECEL LFM LIGHT 8X250G TRT EB BE 67729022 35.2020 5979 3013 0 4469 BECEL LFM ORIG 8X500G TRT EB BE 67729088 29.2020 3730 0 0 3730 BECEL LFM ORIG 8X500G TRT EB BE 67729088 36.2020 32789 8207 0 32789 BECEL LFM ORIGINAL 16X285G TRT EB BE 67729090 34.2020 23084 6230 0 23084 BECEL LFM ORIGINAL 16X285G TRT EB BE 67729090 35.2020 28345 6926 0 22115 CROMA SMELTJUS 250G 20X 67730667 35.2020 4515 3198 0 4515 Hi Anonymous
You can convert your excel formula to dax like this:
Batch consumption = VAR _material = SELECTEDVALUE ( 'Table'[Material] ) VAR _sslWeek = SELECTEDVALUE ( 'Table'[SSL Week] ) VAR _prevSslWeek = CALCULATE ( MAX ( 'Table'[SSL Week] ), FILTER ( ALL ( 'Table' ), 'Table'[SSL Week] < _sslWeek && 'Table'[Material] = _material ) ) VAR _prev2SslWeek = CALCULATE ( MAX ( 'Table'[SSL Week] ), FILTER ( ALL ( 'Table' ), 'Table'[SSL Week] < _prevSslWeek && 'Table'[Material] = _material ) ) VAR _batchConsumptionPrevWeek = CALCULATE ( SUM ( 'Table'[FORECAST] ), FILTER ( ALL ( 'Table' ), 'Table'[Material] = _material && 'Table'[SSL Week] = _prevSslWeek ) ) - CALCULATE ( SUM ( 'Table'[FORECAST] ), FILTER ( ALL ( 'Table' ), 'Table'[Material] = _material && 'Table'[SSL Week] = _prev2SslWeek ) ) RETURN 'Table'[FORECAST] - CALCULATE ( SUM ( 'Table'[FORECAST] ), FILTER ( ALL ( 'Table' ), 'Table'[SSL Week] = _prevSslWeek && 'Table'[Material] = _material ) ) + IF ( 'Table'[scrap_volume] = 0 && NOT ( ISBLANK ( _prevSslWeek ) ), _batchConsumptionPrevWeek - CALCULATE ( SUM ( 'Table'[STOCK] ), FILTER ( ALL ( 'Table' ), 'Table'[Material] = _material && 'Table'[SSL Week] = _sslWeek ) ), 0 )In the sample data there where some (blank)-values. This will be interpreted as text in power bi, and prevent the formula from working. (blank) needs to be changed to null in power query.
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
