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 of Month =
VAR _SKU = SELECTEDVALUE('DEMAND & SUPPLY (3)'[2nd Item Number])
VAR LastDayOfMonth = EOMONTH(MAX('DEMAND & SUPPLY (3)'[Sched Pick].[Date]), 0)
RETURN
    CALCULATE (
        sum ( 'DEMAND & SUPPLY (3)'[Quantity Available ] ), // Replace 'YourTable' and 'Quantity' with your actual table and quantity column names
        FILTER (
            ALL ( 'DEMAND & SUPPLY (3)' ), // Remove any existing filters on 'YourTable'
            'DEMAND & SUPPLY (3)'[Sched Pick].[Date] = LastDayOfMonth && 'DEMAND & SUPPLY (3)'[2nd Item Number]=_SKU
        )
    )

 

 

"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.

24 Replies

  • MasonMA's avatar
    MasonMA
    Super User
    11 months ago

    Hi, 

     

    Looking at your Measure and pictures, I dont see a Date table that's used to filter the fact table. It's always recommended to build a proper Date table and have it connected with your table, in your case the 'DEMAND & SUPPLY (3)'[Sched Pick] column in fact table with one-to-many relationship. This way your MAX(Date) would behave as expected. 

     

    With this all set up, replace your VAR LastDayOfMonth with 
    VAR LastDayOfMonth =
    EOMONTH ( MAX ( 'Date'[Date] ), 0 )

    • ahmer_malick's avatar
      ahmer_malick
      Helper II
      10 months ago

      I tried that but the problem to use this logic is that if the last date is 28 or 29th of the month and the EOD is 30 or 31st it ignores those 

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

        Hi ahmer_malick ,

        Thanks for the clarification. This happens because the EOMONTH() function returns the calendar month-end date (30th or 31st), and if that date doesn’t exist in your data - for example, when the latest record for that month is on the 28th or 29th - the result appears blank.

         

        The approach I shared earlier avoids this by using a “less than or equal to” comparison with the month-end date, so it still returns results for all available dates within each month.

        Could you please try that version and confirm if it gives the expected output across months.

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

    Hi ahmer_malick ,

    Thanks for posting your question in the Fabric Community.

    As MasonMA  mentioned, the issue comes from not having a proper Date table in your model. Creating a dedicated Date table and relating it to 'DEMAND & SUPPLY (3)'[Sched Pick] will allow MAX(Date) to behave as expected and ensure the measure returns results for each month-end, not just the current one.

    This change will make your calculation dynamic across all months.

     

    In addition, to help test and suggest working solutions, and as suggested by tayloramy , could you please provide sample data or sample pbix file, and ensure that it is shared with the appropriate permissions?

    You can find guidance here: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

    This will make it easier to reproduce your scenario and provide more targeted help.

     

    Please reach out for further assistance.

    Thank you.

     

    • ahmer_malick's avatar
      ahmer_malick
      Helper II
      10 months ago

      I tried that but its only giving me 1 month in next 3 years and not MOM basis

    • ahmer_malick's avatar
      ahmer_malick
      Helper II
      10 months ago

      I tried it but its only giving 3 month not the entire information.

      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 ,
    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.

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

         

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

    Hi ahmer_malick ,

    Just checking in to see if you have found a solution for your query. If further assistance is needed, please reach out.

     

    If possible, could you also share a sample PBIX file (without any sensitive information) or some sample data that mimics your scenario? This will help us better understand your requirement and provide possible approaches.

    How to provide sample data in the Power BI Forum - Microsoft Fabric Community

     

    Thank you.