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

Greg_Deckler

Community Champion

6 years ago

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/339586

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.

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.

ReducedBalanceQuantity.pbix55 KB

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!

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!

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?