Forum Discussion

aaron1225's avatar
aaron1225
Helper I
7 years ago
Solved

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

 

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi aaron1225,

     

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

     

    Regards,

    Pradeep

    • aaron1225's avatar
      aaron1225
      Helper I
      7 years ago

      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. 

       

       

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        7 years ago

        aaron1225 ,

         

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

         

         

        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.