Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AMPAllie
Helper II
Helper II

Measure If Date Less than Another Date

I have a Matrix:Matrix.pngMultiple 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":

Excel.png

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.

 

1 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. 

View solution in original post

5 REPLIES 5
v-xicai
Community Support
Community Support

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.

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.  
 
 

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.

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. 

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

 

Untitled.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.