Measure If Date Less than Another Date

[AMPAllie]

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 am able to finagle this in Excel by basically saying 

If(AND(StartofWeek>=Startdate, StartofWeek<=EndDate), "Y", "N") and then conditionally formatting to produce the "bar":

I can't figure out how to write a measure that asks if a date is less than another date in another table. All help appreciated.  it's Easter weekend, so I'll apologize in advance if my promptness in replying is compromised.

[v-xicai]

Hi @AMPAllie ,

You can create new calendar table Calendar and new column StartofWeek, and then create measure Colors. Rename the table and filed to take it effective.

Calendar = CALENDARAUTO()

StartofWeek = WEEKDAY('Calendar '[Date],2)

Colors =

var d=CALCULATE(SELECTEDVALUE('Calendar'[Date]),FILTER(Calendar,'Calendar '[StartofWeek]="1"))

var v=SELECTEDVALUE(Table1[Project])

return IF(Table1[Project]=v&&d<=Table1[EndDate]&&d>=Table1[StartDate],"Y","N")

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AMPAllie]

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.  

[v-xicai]

Hi @AMPAllie ,

Try to change it to "'Calendar'[StartofWeek]=1" in the the measure Colors, and try it again, based on the column Key(2) working fine. Or you can share your more detail sample data or screenshots for further analysis. Do mask sensitive data before uploading.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AMPAllie · Accepted solution]

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. 

[AMPAllie]

Ok, I got it to put the "Y" on the visual. However, it's only putting one for the start date...not the span: