Forum Discussion

AMPAllie's avatar
AMPAllie
Helper II
7 years ago
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...
Need Help
  • AMPAllie's avatar
    AMPAllie
    7 years ago

    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. 

v-xicai's avatar
v-xicai
Community Support
7 years ago

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's avatar
AMPAllie
Helper II
7 years ago

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.  
 
 
  • AMPAllie's avatar
    AMPAllie
    Helper II
    7 years ago

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

     

     

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    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's avatar
      AMPAllie
      Helper II
      7 years ago

      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.