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
Maciek_
Frequent Visitor

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

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.

Maciek__0-1599467462678.png

 

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

1 ACCEPTED SOLUTION
Maciek_
Frequent Visitor

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)
)
)

View solution in original post

3 REPLIES 3
Fowmy
Super User
Super User

@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")

Fowmy_1-1599470683020.png

 

________________________

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

 





Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Maciek_
Frequent Visitor

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
Super User
Super User

@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

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.

Top Solution Authors