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)

     

amitchandak's avatar
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's avatar
pankajj
Helper III
6 years ago

amitchandak 

Worked perfectly!

Thanks a million!

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

       

      • pankajj's avatar
        pankajj
        Helper III
        6 years ago

        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.