Forum Discussion

HChoice's avatar
HChoice
Frequent Visitor
4 years ago
Solved

An argument of function 'DATE' has the wrong data type...

I know there are heaps of similar posts and I promise I've tried different solutions, but for the life of me, I can't work out what I'm doing wrong.

 

I'm attempting to calculate the increase in median price over 20 years. The data will be updated quarterly, so I need to use dynamic dates.

 

The calc works fine in as far as I can get results and graph it, but the moment I try to apply filters to the visual in any way, I get the 'DATE' has the wrong data type error displaying in the visual.

 

20yr Increase =
VAR mdate = MAX('Quarterly Rent'[Quarter Ending])
VAR ndate = DATE(YEAR(mdate) - 20, MONTH(mdate), DAY(mdate))
VAR pay1 = CALCULATE(MIN('Quarterly Rent'[Median]),
FILTER('Quarterly Rent', 'Quarterly Rent'[Quarter Ending] = ndate))
VAR pay2 = CALCULATE(MIN('Quarterly Rent'[Median]),
FILTER('Quarterly Rent', 'Quarterly Rent'[Quarter Ending] = mdate))

RETURN DIVIDE(pay2 - pay1, pay1)
 
Thoughts? Ideas?
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    HChoice , I am assuming Quarter Ending has datatype date and if so measure looks correct.

     

    Need to check what you are adding to visual, because max will work on row context

     

    you can try

    VAR mdate = MAXX(allselected('Quarterly Rent') , 'Quarterly Rent'[Quarter Ending])

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    HChoice , I am assuming Quarter Ending has datatype date and if so measure looks correct.

     

    Need to check what you are adding to visual, because max will work on row context

     

    you can try

    VAR mdate = MAXX(allselected('Quarterly Rent') , 'Quarterly Rent'[Quarter Ending])

    • HChoice's avatar
      HChoice
      Frequent Visitor
      4 years ago

      Thank you!! Sorry for the exceptionally late reply. I'm not entirely sure why it works, but I'll take it.