Need help with DAX Formula

[ahmer_malick]

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

        )

    )

[ahmer_malick]

I am attaching another PBIX file J&D.pbix

[v-veshwara-msft]

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.

[v-veshwara-msft]

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]

I am attaching another PBIX file J&D.pbix

[v-veshwara-msft]

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]

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]

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

I am attaching another PBIX file J&D.pbix

[ahmer_malick]

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

[MasonMA]

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]

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]

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]

Hi @ahmer_malick ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.

[tayloramy]

Hi @ahmer_malick, 

Can you please provide sample data in a useable format (not a screenshot)? 

How to Get Your Question Answered Quickly - Microsoft Fabric Community

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

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

[ahmer_malick]

I am attaching another PBIX file J&D.pbix

[ahmer_malick]

Test PWBI 

[tayloramy]

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.