How to make a column of week over week percent change of total hours

[Anonymous]

Hi,

New Power BI user here. I want to create a new column to show the percent change difference from week to week. I was trying different methods but none have worked. My week# is a column I created with a numeric data type. 

[v-cherch-msft]

Hi @Anonymous 

You may refer to below measure:

Measure =
VAR pre_week_hours =
    CALCULATE (
        SUM ( Data[Hours] ),
        FILTER ( ALL ( Data[Week] ), Data[Week] = MAX ( Data[Week] ) - 1 )
    )
VAR current_week_hours =
    SUM ( Data[Hours] )
RETURN
    IF (
        pre_week_hours = BLANK (),
        BLANK (),
        DIVIDE (
            ABS ( pre_week_hours - current_week_hours ),
            ( pre_week_hours + current_week_hours ) / 2
        )
    )

Regards,

[Anonymous · Accepted solution]

Thank you!

This was my final code. (I updated the formula to find the percent change)

Measure =
VAR pre_week_hours =
    CALCULATE (
        SUM ( Data[Hours] ),
        FILTER ( ALL ( Data[Week] ), Data[Week] = MAX ( Data[Week] ) - 1 )
    )
VAR current_week_hours =
    SUM ( Data[Hours] )
RETURN
    IF (
        pre_week_hours = BLANK (),
        BLANK (),
        DIVIDE (
            ABS ( current_week_hours - pre_week_hours ),
            ( pre_week_hours)
        ) * 100
    )

[Anonymous]

Please be clearer. Have you created a date table? Your function is SAMEPERIODLASTWEEK or similar time intelligence functions

[Anonymous]

I have created a date table that joins to the main table with the data

I was able to do a past year analysis with percent changes with the following formulas:

TotalHoursPY = CALCULATE ( [TotalHours], SAMEPERIODLASTYEAR('v_clndr_day'[ClndrDt]) )

TotalHoursPYDiff = [TotalHours] - [TotalHoursPY]

TotalHoursPYDiff% = 100 * ABS([TotalHoursPY] - [TotalHours]) / (([TotalHoursPY] + [TotalHours])/2)

Since its a week number and its not a date time function how can I achieve this?

Sameperiodlastweek did not not work since it wasn't a valid fuction.

[Anonymous]

That's true, I was wrong. As far as I remember Dax does not have "week" intelligence functions. Look here https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/