Forum Discussion

pankajj's avatar
pankajj
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's avatar
    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.

     

  • amitchandak's avatar
    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's avatar
    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)

     

pankajj's avatar
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-LINEORDER QTYDemand_OrderALLOTTED_QTYPROMISE DATESupply_Date
SO-AB1234/1100SO-AB1234/120April01 2020March10 2020
SO-AB1234/1 SO-AB1234/130April01 2020March20 2020
SO-AB1234/1 SO-AB1234/140April01 2020April20 2020
SO-AB1234/1 SO-AB1234/110April01 2020May15 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-LINEPROMISE DATESupply_DateDemand_OrderORDER QTYALLOTTED_QTYQTY_DIFFERENCEREMAIN_QTYTOTAL_SHIPPED_QTYALERTMessage
SO-AB1234/1April01 2020March10 2020SO-AB1234/110020801000EarlyPartial Quantity(20) Arriving on 03/10/2020 forSO-AB1234/1
SO-AB1234/1April01 2020March20 2020SO-AB1234/1305000EarlyNO SUPPLY DATE
SO-AB1234/1April01 2020April20 2020SO-AB1234/1401000LateNO SUPPLY DATE
SO-AB1234/1April01 2020May15 2020SO-AB1234/110000LateNO 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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
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.

 

  • pankajj's avatar
    pankajj
    Helper III
    6 years ago

    Greg_Deckler 

    Hi Greg!

    This is amazing. 

    Thanks a ton for your help.

    You guys are the rockstars of this community!