Forum Discussion

ahmer_malick's avatar
ahmer_malick
Icon for Helper II rankHelper 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
    10 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.

    • tayloramy's avatar
      tayloramy
      Icon for Super User rankSuper User
      11 months ago

      Hi ahmer_malick, 
      Only users in your org are able to access that file

       

      If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.