Forum Discussion

Helper III

6 years ago

Solved

Calculate Reduced balance quantity

Hi Community! Is there a quick way in Power BI / DAX to calculate remaining quantity? I have following sample table where i need to calculate balance quantity (REMAIN_QTY). In my table i have...

Need Help

Greg_Deckler

6 years ago

So, perhaps you want something like the following:

Column = 
    VAR __PromiseDate = 'Table'[PROMISE DATE]
    VAR __SupplyDate = 'Table'[Supply_Date]
    VAR __TotalSoFar = 
        SUMX(
            FILTER(
                'Table',
                'Table'[Supply_Date] < EARLIER('Table'[Supply_Date])
            ),
            'Table'[ALLOTTED_QTY]
        )
    VAR __OrderQty = 
        SUMX(
            FILTER(
                'Table',
                'Table'[Demand_Order] = EARLIER('Table'[Demand_Order])
            ),
            'Table'[ORDER QTY]
        )
    VAR __PreText = 
        SWITCH(
            TRUE(),
            __TotalSoFar < __OrderQty,"Partial Quantity (" & 'Table'[ALLOTTED_QTY] & ") ",
            __TotalSoFar = __OrderQty,"Final Quantity (" & 'Table'[ALLOTTED_QTY] & ") ",
            BLANK()
        )
    VAR __PostText = 
        SWITCH(
            TRUE(),
            __SupplyDate < __PromiseDate,"Arriving Early on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order],
            __SupplyDate > __PromiseDate,"Arriving Late on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order],
            BLANK()
        )
RETURN
    __PreText & __PostText

PBIX is attached.

ReducedBalanceQuantity.pbix55 KB

amitchandak

6 years ago

Try like

Cal remaining = sumx('Table','Table'[ORDER QTY])-sumx(filter('Table','Table'[ORDER-LINE]=EARLIER('Table'[ORDER-LINE]) && 'Table'[Supply_Date]<=EARLIER('Table'[Supply_Date])),'Table'[ALLOTTED_QTY])

Add additional filter like demand order if needed

Greg_Deckler

6 years ago

Couple necessary fixes to the right supply date field and an equals sign. I think I got your change right as well, PBIX attached.

Column = 
    VAR __PromiseDate = 'Table'[PROMISE DATE]
    VAR __SupplyDate = 'Table'[SupplyDate]
    VAR __TotalSoFar = 
        SUMX(
            FILTER(
                'Table',
                'Table'[SupplyDate] <= EARLIER('Table'[SupplyDate]) &&
                    'Table'[ORDER-LINE] = EARLIER('Table'[ORDER-LINE])
            ),
            'Table'[ALLOTTED_QTY]
        )
    VAR __OrderQty = 
        SUMX(
            FILTER(
                'Table',
                'Table'[Demand_Order] = EARLIER('Table'[Demand_Order]) &&
                    'Table'[ORDER-LINE] = EARLIER('Table'[ORDER-LINE])
            ),
            'Table'[ORDER QTY]
        )
    VAR __PreText = 
        SWITCH(
            TRUE(),
            __TotalSoFar < __OrderQty,"Partial Quantity (" & 'Table'[ALLOTTED_QTY] & ") ",
            __TotalSoFar = __OrderQty,"Final Quantity (" & 'Table'[ALLOTTED_QTY] & ") ",
            BLANK()
        )
    VAR __PostText = 
        SWITCH(
            TRUE(),
            __SupplyDate < __PromiseDate,"Arriving Early on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order],
            __SupplyDate > __PromiseDate,"Arriving Late on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order],
            BLANK()
        )
RETURN
    IF(ISBLANK(__SupplyDate),[Supply_Date],__PreText & __PostText)

shipr.pbix89 KB

pankajj

Helper III

6 years ago

Greg_Deckler

Hi Greg,

Thanks a ton for your quick reply.

Actually I am currently facing a issue with my Power BI File.

My situation is as follows:

I have following sample table:

ORDER-LINE	ORDER QTY	Demand_Order	ALLOTTED_QTY	PROMISE DATE	Supply_Date
SO-AB1234/1	100	SO-AB1234/1	20	April01 2020	March10 2020
SO-AB1234/1		SO-AB1234/1	30	April01 2020	March20 2020
SO-AB1234/1		SO-AB1234/1	40	April01 2020	April20 2020
SO-AB1234/1		SO-AB1234/1	10	April01 2020	May15 2020

Now when we order parts, they sometime arrive in batches and i want to populate a message whenever the parts arrive (i.e. Supply_Date) before the Promise Date or after the Promise Date.

I thought that the solution for that could be to calculate another column which shows the balance quantity and message like:

Partial Quantity (20) Arriving Early on March10 2020 for SO-AB1234/1
Partial Quantity (30) Arriving Early on March20 2020 for SO-AB1234/1
Partial Quantity (40) Arriving Late on April20 2020 for SO-AB1234/1
Final Quantity (10) Arriving Late on May15 2020 for SO-AB1234/1

Not sure if this is the best way.

@amitchandak helped me with following Calculated Column :

Message = if([TOTAL_SHIPPED_QTY]>=[ORDER QTY]
, "FULL QUANTITY SHIPPED" ,
	if(not(ISBLANK([Supply_Date])),
		if([QTY_DIFFERENCE]>0
		&& [REMAIN_QTY]=[ALLOTTED_QTY] ,  "Full Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE] ,
			if([REMAIN_QTY]>[ALLOTTED_QTY] && [QTY_DIFFERENCE]>0 ,
				"Partial Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE] ,
					if([REMAIN_QTY]>[ALLOTTED_QTY] && [QTY_DIFFERENCE] = 0 &&
						"Final Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE]

, "NO SUPPLY DATE",[ALERT])

))))

and advised to relook at the REMAIN_QTY logic.

I am still not able to fix the query.

The query still shows output table as:

ORDER-LINE	PROMISE DATE	Supply_Date	Demand_Order	ORDER QTY	ALLOTTED_QTY	QTY_DIFFERENCE	REMAIN_QTY	TOTAL_SHIPPED_QTY	ALERT	Message
SO-AB1234/1	April01 2020	March10 2020	SO-AB1234/1	100	20	80	100	0	Early	Partial Quantity(20) Arriving on 03/10/2020 forSO-AB1234/1
SO-AB1234/1	April01 2020	March20 2020	SO-AB1234/1	30	50	0	0	Early	NO SUPPLY DATE
SO-AB1234/1	April01 2020	April20 2020	SO-AB1234/1	40	10	0	0	Late	NO SUPPLY DATE
SO-AB1234/1	April01 2020	May15 2020	SO-AB1234/1	10	0	0	0	Late	NO SUPPLY DATE

here is the Power BI File link:

https://drive.google.com/open?id=1j64FMJeZQsZBNDkmL_mDgYsWzbYP10GI

Being a super user I am sure you definately have a solution to my problem.

Looking forward for your help please.

amitchandak

Super User

6 years ago

Try like

Cal remaining = sumx('Table','Table'[ORDER QTY])-sumx(filter('Table','Table'[ORDER-LINE]=EARLIER('Table'[ORDER-LINE]) && 'Table'[Supply_Date]<=EARLIER('Table'[Supply_Date])),'Table'[ALLOTTED_QTY])

Add additional filter like demand order if needed

pankajj

Helper III

6 years ago

amitchandak

Worked perfectly!

Thanks a million!

pankajj

Helper III

6 years ago

amitchandak & Greg_Deckler

Hi Amit & Greg,

Your Solution worked perfect. However I have and additional situation where Supply_Date is not a date column, it also has text (such as STOCK, No Supply) in my original data table. I have added copy of the Supply_Date (SupplyDate) and replaced error with null.

Sadly with this addition, the expected results have changed completely.

Kindly have a look at the updated PBIX file from below link please.

https://drive.google.com/open?id=1Fp8D9cjydsl8F9te66dBW9_V3pwrRSlY

Sorry for the trouble.

Thank you very for your help and valuable time.

Best regards,

Pankajj

Greg_Deckler

Community Champion

6 years ago

OK, I put a simple check in to see if SupplyDate is blank and return blank if so. Technically you could make this anything you wanted in the RETURN but I wasn't entirely certain what you wanted displayed if there is no SupplyDate. Updated PBIX attached.

Column = 
    VAR __PromiseDate = 'Table'[PROMISE DATE]
    VAR __SupplyDate = 'Table'[SupplyDate]
    VAR __TotalSoFar = 
        SUMX(
            FILTER(
                'Table',
                'Table'[Supply_Date] < EARLIER('Table'[Supply_Date])
            ),
            'Table'[ALLOTTED_QTY]
        )
    VAR __OrderQty = 
        SUMX(
            FILTER(
                'Table',
                'Table'[Demand_Order] = EARLIER('Table'[Demand_Order])
            ),
            'Table'[ORDER QTY]
        )
    VAR __PreText = 
        SWITCH(
            TRUE(),
            __TotalSoFar < __OrderQty,"Partial Quantity (" & 'Table'[ALLOTTED_QTY] & ") ",
            __TotalSoFar = __OrderQty,"Final Quantity (" & 'Table'[ALLOTTED_QTY] & ") ",
            BLANK()
        )
    VAR __PostText = 
        SWITCH(
            TRUE(),
            __SupplyDate < __PromiseDate,"Arriving Early on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order],
            __SupplyDate > __PromiseDate,"Arriving Late on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order],
            BLANK()
        )
RETURN
    IF(ISBLANK(__SupplyDate),BLANK(),__PreText & __PostText)

shipr.pbix89 KB

Forum Discussion

Calculate Reduced balance quantity

Related content

Reduce PBIX file size

Stock on Hand reducing quantity

Calculate production time*quantity

calculating forecasted quantity

How to reduce calculation time on a query?