Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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
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:
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |