DAX EOMONTH formula: comparing values in column with an absolute cell references

[Rashidb84]

Hi,

I've created a new Excel file on which I worked yesterday. In it I have added a  DAX formula to compare the current month ends date with the date in column Net due date. This worked fine yesterday. 

But as we moved to a new month today the values changed which I didn't realise yesterday. The end of month date basically for the file shouldn't change automatically. 

=if('Open AP'[Net due date]<=EOMONTH(today(),0),"Yes","No")

I therefore uploaded a new sheet in which I will monthly amend the date value manually. Uploaded this file:

After amending the initial formula to compare the Net due date with the Month end date value I'm getting the following result:

I tried an absolute reference (the usual Excel method), and discovered that it doesn't work in DAX as it references to columns.

I basically want to compare the month end date of the reported month (usually the month we are in, but if the file is opened in a new month the details shouldn't change because we are in a new month) with the Net due date and establish if Net due date is bigger, equal to month end date or the Month end date is bigger.

If absolute reference indeed doesn't work, how can this be solved?

Thank you for your time!!! 

[Rashidb84]

Hi All,

I'm understanding now that DAX doesn;t do absolute cel references like in Excel. Via a measure I will have to create a column with the data in the cel that I want to reference. Basically a column that will repeat the data of the absolute cel reference on each line. 

I will try to work on this tomorrow. Will update this post with pics if I get to a solution.

[Rashidb84 · Accepted solution]

Hi All,

I just figured out how to get this working. I first tried working it out in PowerBI as I found out how it works in PowerBI. The function used was SELECTEDVALUE.

The function Selectedvalue is not available in Excel. Therefore I used the following DAX formula in Excel:

=if(hasonevalue('Date'[Month end date]),values('Date'[Month end date]))

In combination with the following formula to get my result:

=if('Open AP'[Net due date]<='Open AP'[Month end date],"Yes","No")

[Rashidb84]

Hi All,

Sorry for the late reply. I tried to work this out but not sure how this will work. Any idea's?

[lbendlin]

See if you can refactor your approach from  "EOMONTH of latest (current) date"  to "EOMONTH for the latest data date".

[Rashidb84]

Hi Ibendlin,

Thank you for your response.

Not sure if I understand you correctly. I clarified my initial approach above with some additional printscreens. I had ameded the formula from : EOMONTH(today(),0) to refer to the Date tab 'Date'[Month end date].

The result of this was the calculation error shown in my first post (updated pic).