Forum Discussion
Solved
Measure If Date Less than Another Date
I have a Matrix: Multiple unstepped Rows. Column headers are Start of Week from a calendar table. What I need to accomplish is to fill the cells with a color based on the startdate and enddate. I...
No dice on the change. But I did find a workaround. Ii added a custom column in the query to list all dates between the start and end date.
{ Number.From([startdate])..Number.From([enddate])}
then expanded the list and converted to date format. Used a value in the dataset to populate the cells in the visual and then conditionally formatted.
I would still love to know if there is an answer to the original question...this is the long way around.
So I had to change it up a bit and here's what Im using
Colors =
var d=CALCULATE(SELECTEDVALUE('Calendar'[Date]),FILTER(Calendar,'Calendar'[StartofWeek]="1"))
var v=SELECTEDVALUE(RPAssignment[Key(2)])
return IF(MAX(RPAssignment[Key(2)])=v&&d<=RPAssignment[End Date]&&d>=RPAssignment[Start Date],"Y","N")
I had to create the Key(2) column because there were many tasks with the same name and the measure was failing. I After a little manipulation i got an error free measure.
However when I go to add it to the visual I get this error: (Sorry it's not letting me upload images today)
"Calculation error measure 'Calendar'[Colors]: DAX comparison operations do not support comparing values of type Integer with values of type text. Consider using the VALUE of FORMAT function to convert one of the values."
So I changed the StartofWeek to text and added it to the visual. It didn't like that at all, and after 15 minutes of thinking it just put a "Y" in every single cell.
Ok, I got it to put the "Y" on the visual. However, it's only putting one for the start date...not the span: