Translate Excel Formula DAX

[Anonymous]

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". 

[Fowmy]

@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 🙂

YouTube, LinkedIn

[sturlaws]

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.

[Anonymous]

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

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format?  With formula.

In case you looking week vs week, refer

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123