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.
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
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 |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |