Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like the column in this matrix visual where the reporting end of week is the most recent one passed to be highlighted in yellow. For example, today is 2/24 so 2/19 would be the most recent previous end of week date. I looked under conditional formatting but I don't see how to make this work with the options. Any help would be appreciated.
Solved! Go to Solution.
Hi, @Anonymous
You may modify the 'Calendar' table as below.
Calendar =
ADDCOLUMNS(
CALENDARAUTO(),
"EndofWeek",
var d =[Date]
return
MAXX(
FILTER(
CALENDARAUTO(),
YEAR([Date])*100+WEEKNUM([Date])=YEAR(d)*100+WEEKNUM(d)
),
[Date]
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Calendar(a calculated table):
Calendar =
ADDCOLUMNS(
CALENDARAUTO(),
"EndofWeek",
var d =[Date]
return
MAXX(
FILTER(
CALENDARAUTO(),
YEAR([Date])*100+WEEKNUM([Date])=YEAR(d)*100+WEEKNUM(d)
),
[Date]
)-1
)
Relationship:
You may create a measure and set conditional format based on the measure as below.
Color Control =
var d = MAX('Calendar'[EndofWeek])
var lastweeknum =
CALCULATE(
MAX('Calendar'[EndofWeek]),
FILTER(
ALL('Calendar'),
[EndofWeek]<
CALCULATE(
MAX('Calendar'[EndofWeek]),
FILTER(
ALL('Calendar'),
[Date]=TODAY()
)
)
)
)
return
IF(
d = lastweeknum,
"red"
)
Result(Today is 3/1/2021):
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
One thing i noticed about that calendar: one of the days is after the after the end-of-week, every week.
Hi, @Anonymous
You may modify the 'Calendar' table as below.
Calendar =
ADDCOLUMNS(
CALENDARAUTO(),
"EndofWeek",
var d =[Date]
return
MAXX(
FILTER(
CALENDARAUTO(),
YEAR([Date])*100+WEEKNUM([Date])=YEAR(d)*100+WEEKNUM(d)
),
[Date]
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
You can Create a measure using in The Field Value option in Conditional Formatting
if(Selectedvalue(Date)=26 Feb 2021 - 7 , "Red", "")
(for this =26 Feb 2021 - 7
you need to build dynamic field based on the behaviour of your data).
Proud to be a Super User!
I tried this but it won't let me select reportingEndofWeek as a field - maybe because it's a date.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |