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.

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

Hi ahmer_malick ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out. Could you also share a sample PBIX file (without any sensitive information) or some sample data with  appropriate permissions to assist better.
Thank you.

  • ahmer_malick's avatar
    ahmer_malick
    Helper II
    10 months ago

    I am attaching another PBIX file J&D.pbix

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

      Hi ahmer_malick ,

      Thanks for sharing the additional details and sample data.

      I tested the scenario using your structure and confirmed that the measure returns values for the last day of each month when a proper Date table is created and related to fact table.

       

      Please make sure the Date table covers the full data range and is marked as a date table.

      Then I used the below measure:

      Qty Available Last Day of Month =

VAR SelectedEOM = MAX ( 'Calendar'[EOM] )

RETURN

CALCULATE (

    SUM ( 'Sheet1'[Quantity Available] ),

    FILTER (

        ALL ( 'Sheet1' ),

        'Sheet1'[Promise Date] <= SelectedEOM

    )

)

       

      With this setup, the measure returned the month-end quantities for all months present in the data, not just the current one.

       

      Please check if your Date table and relationship match this setup and let us know if you still see limited months in the output.

       

      I have attached .pbix 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

        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