cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emilmaican
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
mahoneypat
Super User IV
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 =
    ADDCOLUMNS (
        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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Super User IV
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Super User IV
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 =
    ADDCOLUMNS (
        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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.