cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aaron1225 Regular Visitor
Regular Visitor

Date Measures Help?

I have used PBI quite a bit but started a new position where I am setting up for the first time in a long while.  For some reason I am having issues with my date fuctions.  Trying to get the endofthemonth function to give me the EOM date, but it seems to pull the last date of the year. Think the solution is how the date table is configured. Any help on this? 

 

Here is the measure. 

https://prnt.sc/mwjbgb

 

Date Table: 

https://prnt.sc/mwjbvs

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Date Measures Help?

@aaron1225 ,

 

I have found the reason, functions like ENDOFMONTH(), ENDOFQUARTER() and ENDOFYEAR() will only return last day in the exist column, not the real ending day. So you can modify your measure using DAX as below to achieve what you want to achieve:

Difference = DATEDIFF(TODAY(), EOMONTH(ENDOFQUARTER(Table1[Date]), 0), DAY)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 REPLIES 7
Pauwnrajpp Member
Member

Re: Date Measures Help?

Hi aaron1225,

 

Your measure and Date Table URL's are not working. Please post more inputs to clarify your problem.

 

Regards,

Pradeep

aaron1225 Regular Visitor
Regular Visitor

Re: Date Measures Help?

Below is my date table, essentially I am trying to determin the number of days remaining in a month so using: 

DaysToEndofMonth = DATEDIFF(TODAY(),ENDOFMONTH(dDate[Date]),DAY) however, this measure returns 294, which is incorrect. 

 

 

Capture.JPG

Community Support Team
Community Support Team

Re: Date Measures Help?

@aaron1225 ,

 

ENDOFMONTH() works well on my side. You can use table visual instead of card visual.

Capture.PNG 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
aaron1225 Regular Visitor
Regular Visitor

Re: Date Measures Help?

@v-yuta-msft thank you, I was able to get this to work, however I can not get the ENDOFMONTH or ENDOFQUARTER to work in my measure. 

 

I am trying to get the number of days from TODAY() to the ENDOFQUARTER.  Using this measure DaysToEndOfQuarter = DATEDIFF(TODAY(),ENDOFQUARTER(dDate[Date]),DAY) it returns an incorrect value. 

Community Support Team
Community Support Team

Re: Date Measures Help?

@aaron1225 ,

 

Could you please show the expected result?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

aaron1225 Regular Visitor
Regular Visitor

Re: Date Measures Help?

@v-yuta-msft I am looking for the number of days remaining in the quarter. So my function should take the last date of the quarter 31-MAR and today so 31-MAR minus 19-MAR, so would return 12 days remaining

Community Support Team
Community Support Team

Re: Date Measures Help?

@aaron1225 ,

 

I have found the reason, functions like ENDOFMONTH(), ENDOFQUARTER() and ENDOFYEAR() will only return last day in the exist column, not the real ending day. So you can modify your measure using DAX as below to achieve what you want to achieve:

Difference = DATEDIFF(TODAY(), EOMONTH(ENDOFQUARTER(Table1[Date]), 0), DAY)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.