Creating a measure based on week numbers as string - no actual dates provided in database

[Maciek_]

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

[Fowmy]

@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 🙂

YouTube  LinkedIn

[Maciek_ · Accepted solution]

Hi,

Thank you for your suggestions. 
Looks like I have managed to figure out correct measure for this issue.

I added first 'IF' to exclude from measure first week of my dataset (week 49th of 2019) and second 'IF' so that for first week of a year, previous value would be considered as a value for 52nd week of a year before.

Then, I applied conditional formatting on values based on this measure (If value 'Difference' <> 0 then formatting).

Difference = IF(MAX(''Column with weeks')=49&&MAX('Column with years')=2019,0,

IF(MAX('Column with weeks')=1,

ROUND(CALCULATE(SUM('Column with values),FILTER(ALL(Table with weeks and years'),'Column with years'=MAX('Column with years')&&'Column with weeks'=1)),2)

-ROUND(CALCULATE(SUM('Column with values),FILTER(ALL('Table with weeks and years'),'Column with years'=(MAX('Column with years)-1)&&'Column with weeks'=52)),2),

ROUND(CALCULATE(SUM('Column with values'),FILTER(ALL('Table with weeks and years'),'Column with years'=MAX('IColumn with years')&&'Column with weeks'=MAX('Column with weeks')-1)),2)

-ROUND(CALCULATE(SUM('Column with values'),FILTER(ALL('Table with weeks and years'),'Column with years'=MAX('Column with years')&&'Column with weeks'=MAX('Column with weeks'))),2)

)

)

[amitchandak]

@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