Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 )
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,
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 )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |