cancel
Showing results for
Did you mean:
Frequent Visitor

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

1 ACCEPTED SOLUTION
Super User IV

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 =
SUMMARIZE (
Table,
Calendar[WeekNo],
Table[Store]
),
"cSales",
CALCULATE (
SUM ( Table[Sale Amount] )
)
)
RETURN
COUNTROWS (
FILTER (
vSummary,
[cSales] >= 3000
)
)

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

3 REPLIES 3
Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Super User IV

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 =
SUMMARIZE (
Table,
Calendar[WeekNo],
Table[Store]
),
"cSales",
CALCULATE (
SUM ( Table[Sale Amount] )
)
)
RETURN
COUNTROWS (
FILTER (
vSummary,
[cSales] >= 3000
)
)

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Frequent Visitor

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

Announcements