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...
  • 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)