Compare Weekly vs Monthly

[Aravind_Kumar]

Hi All : I am filtering week based on (Week num) Calculation and based on selected week previous 3 weeks are displayed in the matrix.

So i bring 4 weeks of actual data but want to compare the last week selected vs budget for that month. 

Below are 3 different examples of matrix movement. 

How to group or keep budget at month to compare for actuals are weekly ? Thank you for input.

Matrix is as below:

Week 4	Week 3	Week 2	Week 1	Budget Monthly	Variance (Budget Monthly vs Week 4)
400	380	410	400	425	25
					(400 - 425 )
Week 5	Week 4	Week 3	Week 2	Budget Monthly	Variance (Budget Monthly vs Week 5)
410	400	380	410	425	15
					(410-425)
Week 6	Week 5	Week 4	Week 3	Budget Monthly	Variance (Budget Monthly vs Week 6)
390	410	400	380	400	10
					(390-400)

[amitchandak · Accepted solution]

@Aravind_Kumar , Try if this month and this week(Based on Rank) Can help

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help
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))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[v-zhangti · Accepted solution]

Hi, @Aravind_Kumar 

You can try the following methods.

Slicer = VALUES('Table'[Week])

Measure = 
IF ( SELECTEDVALUE ( 'Table'[Week] ) >= SELECTEDVALUE ( Slicer[Week] ) - 3
        && SELECTEDVALUE ( 'Table'[Week] ) <= SELECTEDVALUE ( Slicer[Week] ),
    1,
    0
)

Variance = 
Var _N1=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Week]=SELECTEDVALUE(Slicer[Week])))
Var _N2=CALCULATE(SUM('Table'[BudgetMonthly]),FILTER(ALL('Table'),[Week]=SELECTEDVALUE(Slicer[Week])))
Return
_N2-_N1

Result = IF(ISINSCOPE('Table'[Week]),SUM('Table'[Value]),[Variance])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Aravind_Kumar , Try if this month and this week(Based on Rank) Can help

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help
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))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s