% Change in percent only for Last 2 weeks

DAX DAX Calculations
1

Hello Team,

Am trying to create a Matrix table in Power BI where I want the % change to be shown only for last 2 weeks, where 3 date column name will be there and this should be dynamic if new week starts
Sample_file.xlsx (13.3 KB)
, Can you please help to achieve this.

Screenshot and sample file is attached.

image
image734Ă—549 11.4 KB

2

Hi @Dharma,

The extended date table makes this childs play, when using offsets. Instead of hard coding Offset values I’ve added a What if parameter, CompareWeeks.

image
.
Created simple measures to harvest MIN and MAX Values but also setting Defaults.

CompareWeeks Min = 
COALESCE(
    MIN('CompareWeeks'[CompareWeeks]),
    -3
)

CompareWeeks Max = 
COALESCE(
    MAX('CompareWeeks'[CompareWeeks]),
    0
)

.
Added a conditional Count measure

Count = 
VAR StartVal = [CompareWeeks Min]
VAR EndVal = [CompareWeeks Max]
VAR WeekRange = GENERATESERIES( StartVal, EndVal, 1 )
VAR WeeksWithLineage = TREATAS( WeekRange, Dates[WeekOffset] )
VAR Result =
    IF( COUNTROWS( FILTER( Dates, Dates[WeekOffset] IN WeeksWithLineage )) >0,
        COUNTROWS('Sample' )
    )
RETURN
    Result

and a perc change measure

% Change = 
VAR StartVal = [CompareWeeks Min]
VAR EndVal = [CompareWeeks Max]
VAR WeekRange = GENERATESERIES( StartVal, EndVal, 1 )
VAR WeeksWithLineage = TREATAS( WeekRange, Dates[WeekOffset] )
VAR Result =
    IF( COUNTROWS( FILTER( Dates, Dates[WeekOffset] IN WeeksWithLineage )) >0,
        DIVIDE(
            [Count],
            CALCULATE( [Count], DATEADD( Dates[Date], -7, DAY ))
        )
    )
RETURN
    IF( MAX( Dates[WeekOffset] ) <> StartVal && HASONEVALUE( Dates[Fiscal Year & Week] ),
        Result
    )

.
Now you can select Week & Year, WeekOffset or StartOfWeek as column attribute and the period will update dynamically as soon as you refresh the Dates table in the model. You can manually adjust the column width for the first % Change and Total % Change column making them invisible, just disable word wrap in the matrix settings.

image
image732Ă—549 17.4 KB

.

Here’s your sample file.
eDNA - perc change last two weeks.pbix (101.3 KB)

I hope this is helpful

3 Likes
3

Hello @Dharma, good to see that you are having progress with your inquiry. :slight_smile:

Did the response from @Melissa help you solve your inquiry? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Hi @Dharma, we’ve noticed that no response has been received from you since October 26.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @Dharma, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.