cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

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

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?

1 ACCEPTED SOLUTION
Resolver I

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

5 REPLIES 5
Resolver I

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.

Resolver I

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

Resolver I

Hi All,

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

Super User

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

Resolver I

Hi Ibendlin,

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors