Struggling with DATEDIFF

[AndyAtDukes]

Hi, this is my first post and my first report where I am trying dynamic date/time measures and visuals, not much success so far but almost certainly down to user error.

I am trying to find the difference in days between two dates,

the first is a measure:

LastDate = CALCULATE(MAX('Calendar'[Date].[Date]),FILTER(Table1,Table1[Account Manager]<>""))

which appears to be working as when i put this into a card I get a date formatted correct answer.

the second is a column of date values

I think these are properly formatted?

I have then created a new column called "IsMonth" shown above far right.

The function in this column is:

IsMonth = DATEDIFF(Table1[Input Date],[LastDate],DAY)

But the output seems to be zero all the time, I have tried checking out solutions on the forum but can't see what I am doing wrong here.

Incidentally once I have this working I intend to extend the function to create a 1,0 flag in the column with something like:

IsMonth = IF(DATEDIFF(Table1[Input Date],[LastDate],DAY)<30,1,0)

where 1 indicates the Table1[Input Date] is in the last 30 days and 0 otherwise. And then to use this as a filter in visuals.

Thanks in anticipation

Andy

[MattAllington]

It is hard to follow your post. What are you actually  trying to do?

[AndyAtDukes · Accepted solution]

Matt,

Actually I have found another solution to this, I created a column that works out the difference in days between TODAY() and the quotation date. I can then use this to filter any metric on any number of days.

Thanks

Andy

[MattAllington]

The problem with today() is it only works today. What if you want to see Howe many quotes they had "last 30 days" for each month this year on a chart to see a trend?  This won't work using the today approach. 

Take a look at this article I wrote last week to help you get your head around the recommended approach 

http://exceleratorbi.com.au/dax-time-intelligence-beginners/

[AndyAtDukes]

Hi Matt, thanks for your reply.

I have a data set of quotations created by our sales team, I am trying to build a quote performance report which will show;

Last 30, 90 & 180 days rolling number of quotes, value of quotes, conversion ratio etc with year/year comparison.

In addition I have a calendar table with sequential dates, months, years etc

I was trying to create the IsMonth column in order to then be able to filter by this to get that 30 days rolling data.

Hope that makes some sense,

Andy