EOMonth use the filter date instead

[ryan012620]

Can someone help me with this DAX.

CALCULATE(SUM('Leasing Metrics'[Future Occupancy]),'Leasing Metrics'[Date]=EOMONTH('Leasing Metrics'[Date],-1)+1)/CALCULATE(SUM('Leasing Metrics'[Total Units]),'Leasing Metrics'[Date]=EOMONTH('Leasing Metrics'[Date],-1)+1)

Instead of using 'Leasing Metrics'[Date] for EOMONTH, I need to use the 'Date'[Date] which is being use as a filter.

[v-huijiey-msft · Accepted solution]

Hi @ryan012620 ,

Your request is too vague. I hope you can provide me with sample data and expected results. Do not take screenshots. Please remember to erase sensitive data. It would be better if you can provide the pbix file. Remember not to log in to your account on Power BI Desktop when uploading the pbix file.

Instead of using 'Leasing Metrics'[Date] for EOMONTH, You need to use the 'Date'[Date] which is being use as a filter.

First, you need to establish a relationship between the two tables through the Date field in the Leasing Metrics table and the Date table.

Secondly, modify the measure syntax as follows:

CALCULATE(
     SUM('Leasing Metrics'[Future Occupancy]),
     'Leasing Metrics'[Date] = EOMONTH(MAX('Date'[Date]), -1) + 1
)
/
CALCULATE(
     SUM('Leasing Metrics'[Total Units]),
     'Leasing Metrics'[Date] = EOMONTH(MAX('Date'[Date]), -1) + 1
)

You can try using this modified measure to see if it meets your needs.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Ashish_Mathur · Accepted solution]

Hi,

I cannot understand what you want but try this

Measure = DIVIDE(CALCULATE(SUM('Leasing Metrics'[Future Occupancy]),DATESBETWEEN('Date'[Date],min('Date'[Date]),min('Date'[Date])))/CALCULATE(SUM('Leasing Metrics'[Total Units]),DATESBETWEEN('Date'[Date],min('Date'[Date]),min('Date'[Date]))))

[v-huijiey-msft · Accepted solution]

Hi @ryan012620 ,

Your request is too vague. I hope you can provide me with sample data and expected results. Do not take screenshots. Please remember to erase sensitive data. It would be better if you can provide the pbix file. Remember not to log in to your account on Power BI Desktop when uploading the pbix file.

Instead of using 'Leasing Metrics'[Date] for EOMONTH, You need to use the 'Date'[Date] which is being use as a filter.

First, you need to establish a relationship between the two tables through the Date field in the Leasing Metrics table and the Date table.

Secondly, modify the measure syntax as follows:

CALCULATE(
     SUM('Leasing Metrics'[Future Occupancy]),
     'Leasing Metrics'[Date] = EOMONTH(MAX('Date'[Date]), -1) + 1
)
/
CALCULATE(
     SUM('Leasing Metrics'[Total Units]),
     'Leasing Metrics'[Date] = EOMONTH(MAX('Date'[Date]), -1) + 1
)

You can try using this modified measure to see if it meets your needs.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[ryan012620]