Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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". 
Need Help
Fowmy's avatar
Fowmy
Super User
6 years ago

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's avatar
sturlaws
Resident Rockstar
6 years ago

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.