PLS HELP: Consecutive Weekly count not returning cummulative count

[Chava1881]

Hi,

I need to count how many weeks consecutively there is a "stockout" record, the stock out record has two criterias which are: 1) On hand QTY must be Zero and Safety Stock QTY must not be Zero, if these two criterias are met, then the stockout2 column gets a 1 added(Data in Blue). 

The formula should count only the records that are stockout2 = 1 on one day of the week to count weekly, reason why I created the week# column and it's counting only mondays ( or 2), it must also happen only if the field SSTock/Inv is set to "Yes" (orange), and be commulative, so the default filter for the report date will always be the latest date available.

The problem that I am having is that I can create the formula to count the stockout weekly consecutively occurences but only if all dates are selected, if I select the latest date it only counts 1 record which is the selected. 

I was wondering if any of you can help me understand if I am missing a filter + all argument in the table inventory or anything that might bypass the date selection and shows only the cummulative number not the record for the date. Code is below along with screenshot and a link with the pbix called "Test Dashboard".  I feel I am failing in the count argument, but could be totally wrong.

Stockout Consec Weekly Occurrence = 
VAR LatestDateStockout =
    CALCULATE (
        MAX ( 'Inventory'[Report Date] ), Inventory[SSTOCK/INV Match] = "Yes", 'Inventory'[WeekDay#] = 2 ,
        FILTER (
            ALL ( 'Inventory' ),

            'Inventory'[concatenate]
                IN DISTINCT ( 'Inventory'[concatenate] )
                    && 'Inventory'[Stockout 2] <> BLANK () 
        )
    )
VAR LatestSTockOutBlank =
    CALCULATE (
        MAX ( 'Inventory'[Report Date] ), Inventory[SSTOCK/INV Match] = "Yes", 'Inventory'[WeekDay#] = 2 ,
        FILTER (
            ALL ( 'Inventory'  ),
            'Inventory'[concatenate]
                IN DISTINCT ( 'Inventory'[concatenate] )
                    && 'Inventory'[Stockout 2] = BLANK ()
                    
        )
    )

VAR LastDateSTockoutvalidatedblank =
    IF (
        ISBLANK ( LatestSTockOutBlank ),
        CALCULATE (
            MAX ( 'Inventory'[Report Date]  ), Inventory[SSTOCK/INV Match] = "Yes", 'Inventory'[WeekDay#] = 2 ,
            FILTER (
                ALL ( 'Inventory' ),
                'Inventory'[concatenate] IN DISTINCT ( 'Inventory'[concatenate] )
            )
        ),
        LatestSTockOutBlank
    )
VAR StockoutCountVAR =
CALCULATE(count(Inventory[Stockout 2]), DATESBETWEEN(Inventory[Report Date],LastDateSTockoutvalidatedblank,LatestDateStockout),  Inventory[SSTOCK/INV Match] = "Yes",'Inventory'[WeekDay#] = 2)

RETURN
  IF ( LatestDateStockout < LastDateSTockoutvalidatedblank, 0, StockoutCountVAR)

PBIX:
https://drive.google.com/file/d/1b820i4uTL2yHKL-iYAF9We0VUoSIpulI/view?usp=sharing 

Any help on this matter is really appreciated.

[v-easonf-msft · Accepted solution]

Hi, @Chava1881 

Based on the pbix file you provided, I found  you may just set the wrong filter condition in the visual  filter pane. Currently you are filtering 2reportDate into a single date rather than filter the date into a date period. So the consecutive Weekly count  will not return cummulative count.

You can also try to create a measure to filter it:

filter = 
var latestdate=[Z_notblankrec]
return IF(MAX(SSTOCK[2Report Date])<=latestdate,1,0)

The same result you will get 

Please check my attached file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.