Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Rashidb84
Resolver I
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")

Rashidb84_0-1714560471757.png

 

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

 

Rashidb84_0-1714569169599.png

 

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

 

Rashidb84_1-1714569261836.png

 

Rashidb84_2-1714569295351.png

 

 

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!!! 

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

Rashidb84_0-1716212747157.png

 

 

Rashidb84_1-1716213215731.png

 

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

Rashidb84_2-1716214373933.png

 

In combination with the following formula to get my result:

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

Rashidb84_3-1716214404331.png

 

 

 

 

View solution in original post

5 REPLIES 5
Rashidb84
Resolver I
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.

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.

Rashidb84_0-1716212747157.png

 

 

Rashidb84_1-1716213215731.png

 

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

Rashidb84_2-1716214373933.png

 

In combination with the following formula to get my result:

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

Rashidb84_3-1716214404331.png

 

 

 

 

Hi All,

 

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

 

lbendlin
Super User
Super User

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

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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