cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Compare Weekly vs Monthly

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 BudgetMonthly Variance (Budget Monthly vs Week 4) 400 380 410 400 425 25 (400 - 425 ) Week 5 Week 4 Week 3 Week 2 BudgetMonthly Variance (Budget Monthly vs Week 5) 410 400 380 410 425 15 (410-425) Week 6 Week 5 Week 4 Week 3 BudgetMonthly Variance (Budget Monthly vs Week 6) 390 410 400 380 400 10 (390-400)
2 ACCEPTED SOLUTIONS
Super User

@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

Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.