Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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

 





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
Employee
Employee

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
Employee
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

 





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


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.