Matrix filter is filtering out needed results

[jhollingworth]

Hello,

I have a Matrix that I have a filter built to show only items (Used NDC) that meet a dynamic condition. For each item, a formula (Accumulation Selection M) returns a 1 if true and BLANK() if false. This appears to be working.

Currently, the formula looks at the latest date (4/18/24) and compares the quantity (Type SUM) to the previous available date (4/15/24). If the previous date quantity is >=0 and the latest date quantity is < 0, then a 1 returns, else BLANK(). **Basically, I'm looking for items that went from positive/zero to negative.**

The formula is working, here is the data when I download it in the Matrix with no filters:

The issue becomes when I filter out the Matrix to show only values of 1 for (Accumulation Selection M), only two of the items are shown:

I can not figure out why the other two items are not shown (338114403 & 54643564901).

Any help is appreciated and Thank you!

[Anonymous]

Hi @jhollingworth ,

Below is my table:

The following DAX might work for you:

Accumulation Selection M = 
VAR MAXD =  MAX('Accumulations'[Date])
VAR PREVD = CALCULATE( MAX('Accumulations'[Sum]), 'Accumulations'[Date] < MAXD )
VAR EVD = CALCULATE(MIN(Accumulations[Sum]),Accumulations[Date] = MAXD)
RETURN
 IF(EVD < 0 && PREVD >= 0 , 1 , BLANK())

The final output is shown in the following figure:

IF you want to filter Accumulation Selection M, and you can click this:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jhollingworth · Accepted solution]

This helped. I had to make a few adjustments but below is what worked.

Accumulation Selection M =

VAR MAXDSEL = MAXX(SUMMARIZE(ALLSELECTED('Accumulations'[Date]),'Accumulations'[Date],"Max Date", 'Accumulations'[Date]),[Max Date])

VAR PREVD = CALCULATE( MAX('Accumulations'[Date]), 'Accumulations'[Date] < MAXDSEL )

VAR PREVDATE = OFFSET( -1, ORDERBY('Accumulations'[Date] ) )

VAR PREVSUM = CALCULATE( MAX('Accumulations'[Type Sum]), 'Accumulations'[Date] = PREVD )

VAR CURSUM = CALCULATE( MAX('Accumulations'[Type Sum]), 'Accumulations'[Date] = MAXDSEL )

RETURN

SWITCH(

    TRUE(),

    SELECTEDVALUE('Accumulation Measures'[Header#]) = BLANK(),

    1,

    SELECTEDVALUE('Accumulation Measures'[Header#]) = 3,

    IF(

        NOT ISBLANK( PREVSUM ),

            SWITCH(

                TRUE(),

                PREVSUM >= 0 && CURSUM <0, 1

            )

    )

)

Thank you for your help!

[jhollingworth]

An update, 
It appears that the filter is going off the sum of the filter for all the dates.

When I add the other two items, the Acxcumulation Selection M in the Total equals -1.
How can I adjust the filter to only look at the latest date?

Accumulation Selection M =

VAR MAXD = CALCULATE( MAX ('Accumulations'[Date]) )

VAR PREVD = CALCULATE( MAX('Accumulations'[Date]), 'Accumulations'[Date] < MAXD )

VAR PREVSUM = OFFSET( -1, ORDERBY('Accumulations'[Date] ) )

RETURN

SWITCH(

    TRUE(),

    SELECTEDVALUE('Accumulation Measures'[Header#]) = "Negative to Positive",

        IF (

         CALCULATE( [SUM Type SUM (Acc)], PREVSUM ) >= 0 && [SUM Type SUM (Acc)] < 0 ,

          1, -1),