The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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".
@Anonymous
This could be done in Power Query if works for you.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
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 |
@Anonymous , Can you share sample data and sample output in table format? With formula.
In case you looking week vs week, refer
User | Count |
---|---|
161 | |
111 | |
96 | |
87 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |