cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saad_mce
Frequent Visitor

Dax solution required (or power query if that is more appropriate)

Hi all,

I need the help of the wizards in this forum to get a report in powerbi.

I download this data in excel from our ERP system on finished goods SKUs. There are few hundred SKUs.

The data up to column H is from the ERP system. The data starts from today's date to show the on hand stock today. It then goes on to show the stock consumption by customer orders and stock increase by production.

The type of demand is noted in column C and the stock amount consumed by demand or added by production is shown is column G.

 

The solutions are required for the below issues. I want to get the solution in DAX preferably. I want to run this report each Monday with fresh data from system. The spreadsheet has 100s of SKUs sorted by SKU code (column A) and date of activity of that SKU (column D). Column A to H will be from system and the columns I & J should be calculated column in DAX. I am open to get the solutions in measures as well or power query.

 

1. I used a sumifs formula in the 1st calculated column (column I) to calculate the available quantity after each addition/subtraction of stock (SUMIFS(G$2:G2,A$2:A2,$A2)). What formula to use in DAX to get this calculated?

 

2. The 2nd problem is with 2nd calculated column (column J). I want to run a formula that will be blank if we have stock today and "OOS now" if we have zero or negative stock today. It is easy to complete this formula alone, but could not find out how to combine with the next set of problems.

 

3. If we go out of stock in future before next production or between two productions, I want the column to say "POOS before Prod" (potential out of stock). This requires looking at the available Qty column (column I) and Rec/reqd quantity (column G)

 

4. If we have no planned production for this SKU, I want the formula to say "POOS & no Prod Planned". Again the formula needs to look in the negative values of column I and if there are any receipts in column G. 

 

Any suggestion will be greatly appreciated.

 

A

B

C

D

G

I

J

Material

Material Description

MRP element

Planned dates

Rec./reqd quantity

Available Qty

Issues

10007

Product 1

Stock

26/05/2021

                            3,960

              3,960

 

10007

Product 1

Demand

28/05/2021

-                          1,512

              2,448

 

10007

Product 1

Demand

27/08/2021

-                          1,512

                  936

 

10007

Product 1

Demand

27/10/2021

-                          1,512

-                576

POOS before Prod

10007

Product 1

PldOrd

29/10/2021

                            6,048

              5,472

Production

10017

Product 2

Stock

26/05/2021

                                   -  

                     -  

OOS now

10017

Product 2

Demand

28/05/2021

-                        12,096

-          12,096

POOS before Prod

10017

Product 2

PrcOrd

1/06/2021

                          12,096

                     -  

Prod

10017

Product 2

Demand

27/09/2021

-                        12,096

-          12,096

POOS before Prod

10017

Product 2

PldOrd

29/10/2021

                          12,096

                     -  

Prod

10020

Product 3

Stock

26/05/2021

                            5,000

              5,000

 

10020

Product 3

Demand

28/05/2021

-                        12,096

-            7,096

POOS & no Prod Planned

10020

Product 3

Demand

1/06/2021

-                        12,096

-          19,192

POOS & no Prod Planned

10020

Product 3

Demand

27/09/2021

-                        12,096

-          31,288

POOS & no Prod Planned

10020

Product 3

Demand

29/10/2021

-                        12,096

-          43,384

POOS & no Prod Planned

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @saad_mce ,

 

Add the following two columns to your model:

 

 

 

Available Qty =
CALCULATE (
    SUM ( 'Table'[Rec./reqd quantity] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Planned dates] <= EARLIER ( 'Table'[Planned dates] )
            && 'Table'[Material] = EARLIER ( 'Table'[Material] )
    )
)

 

 

 

 

 

Issues = 
VAR nextproduction =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Material] = EARLIER ( 'Table'[Material] )
                && 'Table'[Planned dates] >= EARLIER ( 'Table'[Planned dates] )
                && 'Table'[MRP element] IN { "PldOrd", "PrcOrd" }
        ),
        'Table'[Rec./reqd quantity]
    )
RETURN
    SWITCH (
        TRUE (),
        'Table'[MRP element] IN { "PldOrd", "PrcOrd" }, "Production",
        'Table'[Available Qty] > 0, BLANK (),
        'Table'[Rec./reqd quantity] = 0, "OSS Now",
        nextproduction > 0, "POOS before Prod",
        "Poos & no Prod Planned"
    )

 

 

MFelix_0-1622122455564.png

 

See PBIX file attach.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
saad_mce
Frequent Visitor

Thank you Miguel. Great solution.

MFelix
Super User
Super User

Hi @saad_mce ,

 

Add the following two columns to your model:

 

 

 

Available Qty =
CALCULATE (
    SUM ( 'Table'[Rec./reqd quantity] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Planned dates] <= EARLIER ( 'Table'[Planned dates] )
            && 'Table'[Material] = EARLIER ( 'Table'[Material] )
    )
)

 

 

 

 

 

Issues = 
VAR nextproduction =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Material] = EARLIER ( 'Table'[Material] )
                && 'Table'[Planned dates] >= EARLIER ( 'Table'[Planned dates] )
                && 'Table'[MRP element] IN { "PldOrd", "PrcOrd" }
        ),
        'Table'[Rec./reqd quantity]
    )
RETURN
    SWITCH (
        TRUE (),
        'Table'[MRP element] IN { "PldOrd", "PrcOrd" }, "Production",
        'Table'[Available Qty] > 0, BLANK (),
        'Table'[Rec./reqd quantity] = 0, "OSS Now",
        nextproduction > 0, "POOS before Prod",
        "Poos & no Prod Planned"
    )

 

 

MFelix_0-1622122455564.png

 

See PBIX file attach.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!