Calculate Reduced balance quantity

[pankajj]

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 Order Quantity as 100 and each time the quantity is issued to the Sales Order, I want to record it as balance (REMAIN_QTY) to fulfill the sales order requirement. 

ORDER-LINE	ORDER QTY	Demand_Order	ALLOTTED_QTY	REMAIN_QTY
SO-AB1234/1	100	SO-AB1234/1	20	80
SO-AB1234/1		SO-AB1234/1	30	50
SO-AB1234/1		SO-AB1234/1	40	10
SO-AB1234/1		SO-AB1234/1	10	0

It is so very easy in Excel, any quick way to do the same here in Power BI?

Will really appreciate a quick fix please.

Thank you community in advance.

[Greg_Deckler · Accepted solution]

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.

[amitchandak · Accepted solution]

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]

I do not see a way of doing this without an index or date column or some other way of identifying rows that are earlier than other rows. If you had that, you could create your column with EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[pankajj]

@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 · Accepted solution]

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]

@amitchandak 

Worked perfectly!

Thanks a million!

[pankajj]

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

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)

[pankajj]

@Greg_Deckler 

Hi Greg!

This is perfect, I have replaced Blank() with [Supply_Date].

One more thing, the column is still  showing Partial Quantity (10) Arriving Late on May15 2020 for SO-AB1234/1 instead of Final Quantity(10) Arriving on May15 2020 forSO-AB1234/1.

Kindly check.

Sorry Im asking too much.

[Greg_Deckler · Accepted solution]

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)

[pankajj]

@Greg_Deckler 

Hi Greg,

This works perfect now.

Have tried it with my big data set and runs smoothly.

Thanks a ton for your support. 🙏

You are a genius!

[Greg_Deckler · Accepted solution]

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.

[pankajj]

@Greg_Deckler 

Hi Greg!

This is amazing. 

Thanks a ton for your help.

You guys are the rockstars of this community!