Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
It is hard to follow your post. What are you actually trying to do?
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
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/
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
60 | |
20 | |
18 | |
18 | |
9 |