Calculation of fields from any selected weeks

[Arti12]

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

Divide =
VAR maxWeekinYear = MAX ( Data[WeekInYear] )
VAR minWeekinYear = MIN ( Data[WeekInYear] )
RETURN
IF (
HASONEVALUE (Data[WeekinYear] ), BLANK (),
divide (CALCULATE ([NumberofEmployees], FILTER ( Data, Data[WeekinYear] = maxWeekinYear ))
- CALCULATE ([NumberofEmployees], FILTER ( 'Data', Data[WeekinYear] = minWeekinYear)),CALCULATE ( [NumberofEmployees], FILTER ( 'Data', Data[WeekinYear] = minWeekinYear)))

What shoul I change here? And how to build the subtraction formula?

Thanks in advance!

[Arti12]

@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.

[v-rzhou-msft · Accepted 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]

@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