cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

2020-07-23 09_31_02-Window.png

4 REPLIES 4
Highlighted
Super User IV
Super User IV

@YauTian , 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-La...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Regular Visitor

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)

 

DescMaterialSSL WeekFORECASTSTOCKscrap_volumeBatch consumption
CARTE D'OR CREME DOLCE 500ML 12X18696601(blank)(blank)00#VALUE!
RAMA DCA 500ML 12X6781827740.20206965240696
BB CCA15 8X1L CT EB FR/NL6787656242.202020615217020615
SOLO MP31 8X1L CT EB BE6787657040.20206477006477
SOLO MP31 8X1L CT EB BE6787657043.2020830533108305
SOLO CCA15 8X1L CT EB BE6787657242.2020182231455018223
SOLO CF CCA15 12X250ML BOT EB BE6789903234.20202146108102146
FLORA CCA15 ND 1L 8X6794628440.202015318027153
FLORA MP35 ND 8X1L BRI EB DE NL BE6794629937.2020105041601050
ZEEUWS MEISJE WIKKEL 500G 20X6760787834.20203926112803926
ZEEUWS MEISJE WIKKEL 500G 20X6760787837.2020460699903478
ZEEUWS MEISJE WIKKEL 500G 20X6760787839.2020460630165372479
ZEEUWS MEISJE MARGARINE 250G 20X6761513832.20201827714872018277
BERTOLLI MEL MLD BUT OO 8X225G TBT EB NL6772251733.20209089210809089
BERTOLLI MEL MLD BUT OO 8X225G TBT EB NL6772251736.2020147614860012653
BECEL GOLD MARG 8X250G TRT EB BE6772254733.202021296702129
BECEL GOLD MARG 8X250G TRT EB BE6772254737.20203827199303760
BERTOLLI WIKKEL ONGEZOUTEN 250G 20X6772724332.2020494451904944
BECEL LFM LIGHT 24X250G TRT EB BE6772901329.20201318001318
BECEL LFM LIGHT 24X250G TRT EB BE6772901335.2020101484356010148
BECEL LFM LIGHT 8X250G TRT EB BE6772902231.20202263002263
BECEL LFM LIGHT 8X250G TRT EB BE6772902233.20204097151004097
BECEL LFM LIGHT 8X250G TRT EB BE6772902235.20205979301304469
BECEL LFM ORIG 8X500G TRT EB BE6772908829.20203730003730
BECEL LFM ORIG 8X500G TRT EB BE6772908836.2020327898207032789
BECEL LFM ORIGINAL 16X285G TRT EB BE6772909034.2020230846230023084
BECEL LFM ORIGINAL 16X285G TRT EB BE6772909035.2020283456926022115
CROMA SMELTJUS 250G 20X6773066735.20204515319804515
Highlighted
Community Champion
Community Champion

@YauTian 

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

Highlighted

Hi @YauTian 

 

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors