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 Guys,
I have a job to do. I have a number of employees broken down by regions. For each region, I calculate the difference in the number of employees (a week smaller - a week larger eg week 4 to week 6) and the percentage change (employee difference / to the smaller week).
I would like to make a measure that will do the same to me, but in whatever week I choose. For example, I choose week 52 and calculate it in relation to the week, e.g. 5. Now my measure would calculate week 5 to 52. The point is that I could choose any combinations of weeks and that the values would be calculated automatically for them.
Whis is my measure for % difference
Solved! Go to Solution.
Hi @Arti12 ,
I think your code will work if you transform your data model like mine. Here I build a sample to have a test.
Data Table:
Date table:
Date =
ADDCOLUMNS (
CALENDARAUTO (),
"WeekNum", WEEKNUM ( [Date], 2 ),
"WeekNumName",
"Week" & ""
& WEEKNUM ( [Date], 2 )
)
Relationship:
Measure is similar like yours. I think you use Hasonevalue function to show diff% in Subtotal.
Diff% =
VAR _maxWeekinYear = MAX ( 'Date'[WeekNum] )
VAR _minWeekinYear = MIN ( 'Date'[WeekNum] )
VAR _NumberinMax = CALCULATE ([NumberofEmployees], FILTER ( 'Date','Date'[WeekNum]=_maxWeekinYear))
VAR _NumberinMin = CALCULATE ([NumberofEmployees], FILTER ( 'Date','Date'[WeekNum]=_minWeekinYear))
VAR _DiffPercentage = DIVIDE(_NumberinMax-_NumberinMin,_NumberinMin)
RETURN
IF(HASONEVALUE('Date'[WeekNumName]),BLANK(),_DiffPercentage)
Build a matrix , turn off word wrap function in Format then reduce the width of columns you don't need to 0. Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@amitchandak , is this the easiest way to do this? All I need to have is to calculate the measures based on dynamic weeks. If I will choose week4 and week3 I wish to have calculated 4 and 3, if 6 and 9, the difference between 6 and 9 etc.
Hi @Arti12 ,
I think your code will work if you transform your data model like mine. Here I build a sample to have a test.
Data Table:
Date table:
Date =
ADDCOLUMNS (
CALENDARAUTO (),
"WeekNum", WEEKNUM ( [Date], 2 ),
"WeekNumName",
"Week" & ""
& WEEKNUM ( [Date], 2 )
)
Relationship:
Measure is similar like yours. I think you use Hasonevalue function to show diff% in Subtotal.
Diff% =
VAR _maxWeekinYear = MAX ( 'Date'[WeekNum] )
VAR _minWeekinYear = MIN ( 'Date'[WeekNum] )
VAR _NumberinMax = CALCULATE ([NumberofEmployees], FILTER ( 'Date','Date'[WeekNum]=_maxWeekinYear))
VAR _NumberinMin = CALCULATE ([NumberofEmployees], FILTER ( 'Date','Date'[WeekNum]=_minWeekinYear))
VAR _DiffPercentage = DIVIDE(_NumberinMax-_NumberinMin,_NumberinMin)
RETURN
IF(HASONEVALUE('Date'[WeekNumName]),BLANK(),_DiffPercentage)
Build a matrix , turn off word wrap function in Format then reduce the width of columns you don't need to 0. Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Arti12 , Based on what I got. To travel across year , you can use week rank and use filer on those
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
var _max1 = maxx(allselected('Table'), 'Table'[date])
var _max = maxx(filter(all(date), date[date] = _max1), week[Rank])
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=_max-4 && 'Date'[Week Rank]<=_max))
// Do not use all if you have not selected a week
Also refer
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |