Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I am doing a report on something I thought would be so simple but it's not working.
I have to show Cases opened in the week, compared to last week (there are filters they will have by team or indiv or other).
So I need a fairly simple calc. The way I am calculating Total Number of Cases is by a measure:
Total Number of Cases =
CALCULATE(
Count(VW_Cases[CaseCreatedOn])
)
There are quite a few Dax formulas that offer to return previous week I have found that will not let me use that measure in so wondering if it should be a calc column.
(I'm questioning everything)
1. Can anyone provide this simple formula for me and tell me what I am doing wrong? I want a simple line chart showing this week and last week.
2. Also, what is the formula for % week var over last week for a card? like change in how many cases created (%)
Solved! Go to Solution.
Hi @electrobrit
You may add a week of year column first in Query Editor. Then you may refer to below measure to get the table as requested.
LastWeekTotal = CALCULATE ( COUNT ( VW_Cases[CaseCreatedOn] ), FILTER ( ALL ( VW_Cases ), VW_Cases[Week of Year] = MAX ( VW_Cases[Week of Year] ) - 1 ) )
WeekOverWeek = VAR thisweek = CALCULATE ( COUNT ( VW_Cases[CaseCreatedOn] ), FILTER ( ALL ( VW_Cases ), VW_Cases[Week of Year] = MAX ( VW_Cases[Week of Year] ) ) ) VAR lastweek = CALCULATE ( COUNT ( VW_Cases[CaseCreatedOn] ), FILTER ( ALL ( VW_Cases ), VW_Cases[Week of Year] = MAX ( VW_Cases[Week of Year] ) - 1 ) ) RETURN DIVIDE ( thisweek - lastweek, lastweek )
Regards,
Cherie
Hi @electrobrit
You may add a week of year column first in Query Editor. Then you may refer to below measure to get the table as requested.
LastWeekTotal = CALCULATE ( COUNT ( VW_Cases[CaseCreatedOn] ), FILTER ( ALL ( VW_Cases ), VW_Cases[Week of Year] = MAX ( VW_Cases[Week of Year] ) - 1 ) )
WeekOverWeek = VAR thisweek = CALCULATE ( COUNT ( VW_Cases[CaseCreatedOn] ), FILTER ( ALL ( VW_Cases ), VW_Cases[Week of Year] = MAX ( VW_Cases[Week of Year] ) ) ) VAR lastweek = CALCULATE ( COUNT ( VW_Cases[CaseCreatedOn] ), FILTER ( ALL ( VW_Cases ), VW_Cases[Week of Year] = MAX ( VW_Cases[Week of Year] ) - 1 ) ) RETURN DIVIDE ( thisweek - lastweek, lastweek )
Regards,
Cherie
i like this for WTD but is there a way to see it by day as well? I would like to do a line chart showing daily this week compared to last week same day
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
82 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
129 | |
108 | |
63 | |
55 |