Count if value = 1, >= 5 times last 8 weeks for specific store

[Rosenqvist]

HI, 

Prologue: 

I am new to Power BI but have some experience from writing formulas in Excel. For some parts of this PBI-report i have used ChatGPT with success, but perhaps without the deeper understanding of why it works 🙂 Since im on a trial and error here, basically all calculated columns has a sibling in a measure, to see which one that works. Formulas that starts with 1=measures and 2=columns.

Purpose:

Im building a database that has the purpose to detect customers whos claims are deviating from the average based on last 8 weeks data

Data structure:

All data is basically bundled up in one table and a row of data is based on a specific day, customer, article and reason code (either sale or specific claims code (ex. damaged goods))

The table contains the columns (and alot more but not perhaps relevant to the case):

Customer Number

claims amount

sales amount

Date and Week

Sales category bases on sales per week

followed by

Claims/sales for single store > Claims/sales for all stores in that category*factor = 1/0

"

2Claims/Revenue exceeds sales category by factor =

VAR StoreWeekCat_ClaimRev =

    CALCULATE (

        SUM ( 'Main data table'[Value of Complained Orderline] )

            / SUM ( 'Main data table'[Value of Delivered Goods] ),

        ALLEXCEPT (

            'Main data table',

            'Main data table'[Customer Number],

            'Main data table'[Week],

            'Main data table'[2Store rev cat based on SUM sales per week and customer]

        )

    )

VAR AllStoresWeekCat_ClaimRev =

    CALCULATE (

        SUM ( 'Main data table'[Value of Complained Orderline] )

            / SUM ( 'Main data table'[Value of Delivered Goods] ),

        ALLEXCEPT (

            'Main data table',

            'Main data table'[Week],

            'Main data table'[2Store rev cat based on SUM sales per week and customer]

        )

    )

RETURN

    IF (

        AND (

            [1Store rev cat based on SUM sales per week and customer measure] = "0-59 999",

            ( StoreWeekCat_ClaimRev > ( AllStoresWeekCat_ClaimRev * 4 ) )

        ),

        1,

        IF (

            AND (

                [1Store rev cat based on SUM sales per week and customer measure] = "60 000-199 999",

                ( StoreWeekCat_ClaimRev > ( AllStoresWeekCat_ClaimRev * 2 ) )

            ),

            1,

            IF (

                AND (

                    [1Store rev cat based on SUM sales per week and customer measure] = "200 000-349 999",

                    ( StoreWeekCat_ClaimRev > ( AllStoresWeekCat_ClaimRev * 1.5 ) )

                ),

                1,

                IF (

                    AND (

                        [1Store rev cat based on SUM sales per week and customer measure] = "350 000-649 999",

                        ( StoreWeekCat_ClaimRev > ( AllStoresWeekCat_ClaimRev * 1.5 ) )

                    ),

                    1,

                    IF (

                        AND (

                            [1Store rev cat based on SUM sales per week and customer measure] = ">=650 000",

                            ( StoreWeekCat_ClaimRev > ( AllStoresWeekCat_ClaimRev * 1.5 ) )

                        ),

                        1,

                        BLANK()

                    )

                )

            )

        )

    )

"

Claims in amount exceeds a threshold for that category = 1/0

Both rules = 1, 1/0

Note: I changed all formulas from BLANK() -> 0 from here on out, to be able to visualize the result.

The formulas add up in regards of a single stores claims/sales, as well as the total for that stores sales category. The formulas also work in regards of one store exceeding both claims in amount and regarding claimed/sales in comparison to sales category. When visualizing this, first to evaluate if the rules add up (same store for all rows down below):

Same store but now only with both rules = 1 (called checkfactor in the report), and by week

Now the part i need your help with. I want to create a measure that compiles/sums up all those =1 for "checkfactor" for the last 8 weeks and either give me a = Yes if the count of these are more than 5 in the last 8 weeks, or simply just a sum. If my last image had the right filter (last 8 weeks) i basically would like the total in the image but in a separate measure. The total has to be per customer and a week can only render 1/0, not per row, day other something else.

Any ideas?

Thanks in advance!  

[Rosenqvist · Accepted solution]

Solved it by creating a relative week calendar and calculated each week individually and then summed it up. 

[Rosenqvist]

I have gotten this far which limits the period but unfortunately i get 1, even though only one week is 1 rather than 5 times last 8 weeks.

2CheckTotalFactor Measure 3 =

VAR Last8Weeks =

    CALCULATETABLE (

        VALUES ( 'Main data table'[Filter Claims creation date]),

        DATESBETWEEN('Main data table'[Filter Claims creation date],TODAY()-49,TODAY()

    ))

VAR CustomerCheckTotalFactor =

    CALCULATE (COUNTAX('Main data table',[1Checktotalfactor]),FILTER('Main data table',[1Checktotalfactor]=1),

        'Main data table','Main data table'[Filter Claims creation date] IN Last8Weeks)

RETURN

    IF(CustomerCheckTotalFactor> 4, 1, 0)