Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am fairly new to PowerBI and got stuck on the following issue.
I wanted to apply conditional formatting on the values in my matrix (year --> week number hierarchy in columns and project names in rows) if value for certain week is different than value for previous week.
The thing is that I do not have actual dates for that but I am operating on week number extracted from the string with format "YYYY WW" --> this column is split to extract year and week number in separate columns. This format is unchangeable as this is source formatting.
Also, in this case I am not able to edit anything in Query Editor so this would probably have to be solved with measure and conditional formatting on values based on such measure.
Below is a data sample - values that should be formatted are marked yellow.
I tried to apply the following measure but it seems to be way off and would include only last week anyway.
Difference = CALCULATE(SUM(‘Values column’),FILTER(‘Table containing Week no column’,’Week no column’=WEEKNUM(TODAY(),2)),FILTER(‘Table containing Year column’,'Year column’=YEAR(TODAY()))) – CALCULATE(SUM(‘Values column’),FILTER(‘Table containing Week no column’,’Week no column’=WEEKNUM(TODAY(),2)-1),FILTER(‘Table containing Year column’,'Year column’=YEAR(TODAY())))
I would appreciate any help on that and of course will provide more details when necessary.
Thank you,
Maciek
Solved! Go to Solution.
@Maciek_
Create the following measure and apply on the value as Conditional formatting:
CF_VALUE =
VAR W = SELECTEDVALUE(Table5[Week])
VAR Y = SELECTEDVALUE(Table5[Year])
VAR V = SELECTEDVALUE(Table5[Value])
VAR PV =
CALCULATE(
MAX(Table5[Value]),
Table5[Week] < W,
Table5[Year] = Y,
ALLEXCEPT(Table5,Table5[Project])
)
RETURN
IF( V <> PV && NOT ISBLANK(PV), "YELLOW")
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Maciek_ , My advice would be to unpivot the data and week and year in rows. Also, create a week year table. This we will use like date table
Now create a Rank
Week Year = [Year]&[Week]
Week Rank = RANKX(all('Date'),'Date'[Week Year],,ASC,Dense)
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
You can refer : https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
I doubt your raw format. Can share output you need
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |