Forum Discussion

ahmer_malick's avatar
ahmer_malick
Helper II
11 months ago
Solved

Need help with DAX Formula

  Hi  I need help for a formula. I am only getting EOM for the current month using this formula. How can i get for the remaining months as well.   I am currently using  Qty Available Last Day o...
"DAX "
end of month
  • v-veshwara-msft's avatar
    v-veshwara-msft
    9 months ago

    Hi ahmer_malick ,

    Thanks for the update and additional details.

    Please try the below updated measure, which evaluates the last available Promise Date per Branch Plant and SKU within each month and returns the corresponding Quantity Available value:

    Qty Available Last Actual Day In Month =
VAR _SelEOM = MAX ( 'Calendar'[EOM] )
VAR _LastDateInMonth =
    CALCULATE (
        MAX ( 'Sheet1'[Promise Date] ),
        ALLEXCEPT ( 'Sheet1', 'Sheet1'[Branch/ Plant], 'Sheet1'[Parent 2nd Item Number] ),
        'Sheet1'[Promise Date] <= _SelEOM,
        'Sheet1'[Promise Date] > EOMONTH ( _SelEOM, -1 )
    )
RETURN
CALCULATE (
    SUM ( 'Sheet1'[Quantity Available] ),
    FILTER (
        ALLEXCEPT ( 'Sheet1', 'Sheet1'[Branch/ Plant], 'Sheet1'[Parent 2nd Item Number] ),
        'Sheet1'[Promise Date] = _LastDateInMonth
    )
)

     Please let us know if it gives the expected output for all SKUs across branches.

     

    Thank you.

ahmer_malick's avatar
ahmer_malick
Helper II
10 months ago

If you see in the table and the calculation, it is not working. Last line for 10/31/2025 shows 378.5 but with the current formula , its summing it

 

 

 

 

v-veshwara-msft's avatar
v-veshwara-msft
Community Support
10 months ago

Hi ahmer_malick ,

Thanks for the update.

Here’s the updated measure which returns only the values for the latest available date within each month.

Qty Available Last Actual Day In Month = 
VAR _SKU = SELECTEDVALUE( 'Sheet1'[Parent 2nd Item Number] ) 
VAR _SelEOM = MAX( 'Calendar'[EOM] )
VAR _LastDateInMonth =
    CALCULATE(
        MAX( 'Sheet1'[Promise Date] ),
        ALL( 'Sheet1' ),
        'Sheet1'[Promise Date] <= _SelEOM,
        'Sheet1'[Promise Date] > EOMONTH( _SelEOM, -1 ),
        IF( NOT( ISBLANK( _SKU ) ),
            'Sheet1'[Parent 2nd Item Number] = _SKU,
            TRUE()
        )
    )
RETURN
IF(
    ISBLANK( _LastDateInMonth ),
    BLANK(),
    CALCULATE(
        SUM( 'Sheet1'[Quantity Available] ),
        ALL( 'Sheet1' ),
        'Sheet1'[Promise Date] = _LastDateInMonth,
        IF( NOT( ISBLANK( _SKU ) ),
            'Sheet1'[Parent 2nd Item Number] = _SKU,
            TRUE()
        )
    )
)

Attaching a screenshot and the Power BI file for reference.

 

Hope this helps. Please reach out for further assistance.

Thank you.

 

  • ahmer_malick's avatar
    ahmer_malick
    Helper II
    10 months ago

    Hi 
    The formula is working for 1 particular sku. If I am doing by branch plant for all the month for all of the sku , its only taking max of 1 sku only . I did remove SKU as well just to check but didnt work

    • ahmer_malick's avatar
      ahmer_malick
      Helper II
      10 months ago

       

      • v-veshwara-msft's avatar
        v-veshwara-msft
        Community Support
        9 months ago

        Hi ahmer_malick ,

        Thanks for the update and additional details.

        Please try the below updated measure, which evaluates the last available Promise Date per Branch Plant and SKU within each month and returns the corresponding Quantity Available value:

        Qty Available Last Actual Day In Month =
VAR _SelEOM = MAX ( 'Calendar'[EOM] )
VAR _LastDateInMonth =
    CALCULATE (
        MAX ( 'Sheet1'[Promise Date] ),
        ALLEXCEPT ( 'Sheet1', 'Sheet1'[Branch/ Plant], 'Sheet1'[Parent 2nd Item Number] ),
        'Sheet1'[Promise Date] <= _SelEOM,
        'Sheet1'[Promise Date] > EOMONTH ( _SelEOM, -1 )
    )
RETURN
CALCULATE (
    SUM ( 'Sheet1'[Quantity Available] ),
    FILTER (
        ALLEXCEPT ( 'Sheet1', 'Sheet1'[Branch/ Plant], 'Sheet1'[Parent 2nd Item Number] ),
        'Sheet1'[Promise Date] = _LastDateInMonth
    )
)

         Please let us know if it gives the expected output for all SKUs across branches.

         

        Thank you.