Forum Discussion
Calculate Active Weeks based on a min amount
Hi all,
I have this table with the daily SaleAmount for each Stoare for 1 full year
Date Store SaleAmount
01/01/2020 St1 90
01/01/2020 St2 1500
01/01/2020 St3 90
02/01/2020 St1 100
02/01/2020 St2 700
02/01/2020 St3 300
....
31/12/2020 St3 1470
I'm trying to write a measure that calculates number of weeks in which each store sold more than 3000 per each week of the year called GoodWeeks.
So, if I am to build a table with the list of stores and this measure the result should be something like this
Store No of GoodWeeks
St 1 32
St 2 45
St 3 21
I would prefer not to add any column to my table
Thanks
PS: The model has also a Calendar table with an already calculated column WeekNo
Please try a measure expression like this, replace Table with your Sales table name. I also summarized over the Store too, so your total should also be correct.
Weeks Over 3000 =
VAR vSummary =
ADDCOLUMNS (
SUMMARIZE (
Table,
Calendar[WeekNo],
Table[Store]
),
"cSales",
CALCULATE (
SUM ( Table[Sale Amount] )
)
)
RETURN
COUNTROWS (
FILTER (
vSummary,
[cSales] >= 3000
)
)Pat
3 Replies
- mahoneypat
Microsoft Employee
Please try a measure expression like this, replace Table with your Sales table name. I also summarized over the Store too, so your total should also be correct.
Weeks Over 3000 =
VAR vSummary =
ADDCOLUMNS (
SUMMARIZE (
Table,
Calendar[WeekNo],
Table[Store]
),
"cSales",
CALCULATE (
SUM ( Table[Sale Amount] )
)
)
RETURN
COUNTROWS (
FILTER (
vSummary,
[cSales] >= 3000
)
)Pat
Thanks Pat for the quick answer. It works for this example!
I have another question: If I need to use the measure in a context in which I use year as a column filter, it doesn't seem to work anymore.
Can I update the measure to corectly calculate the figures in a context of another filter (like split by Year)?
For example:
2020 2021
Store No of GoodWeeks No of GoodWeeks
St 1 32 5
St 2 45 3
St 3 21 2
- mahoneypat
I would have expected that measure to work even within a Year context (if the Year column is also from your Calendar table). Can you show where it broke down or explain further? Adding a link (OneDrive/Google Drive/etc) to your pbix (with mock data) would be best way to help troubleshoot.
Pat
