Calculate Active Weeks based on a min amount

[emilmaican]

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

[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

[mahoneypat · Accepted solution]

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

[emilmaican]

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