Help with counting when comparing to previous columns

[jhollingworth]

Hello,
I'm hoping this is simple, but I can't seem to figure it out.

I have a sheet of data that has the number on hand of items based on dates:

Date	Items	Count Sum
4/18/2024	Bike	5
4/18/2024	Car	-1
4/18/2024	Motorcycle	-3
4/10/2024	Bike	-1
4/10/2024	Car	1
4/10/2024	Motorcycle	2
4/1/2024	Bike	5
4/1/2024	Car	2
4/1/2024	Motorcycle	-2

I'm trying to make a Matrix in Desktop that compares the Count Sum of one date to the previous and tells me if it switched from Positive to Negative or Negative to Positive:

Type	4/1/2024	4/10/2024	4/18/2024
Total Positive	1	2	1
Total Negative	2	1	2
Positive to Negative	n/a	1	2
Negative to Positive	n/a	1	1

I have the Total Positive and Total Negative working for each date but I am having trouble figuring out a formula the will evaluate each item to see if it went from positive to negative and then negative to positive.
This is as close as I have gotten:

Value =

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

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

RETURN

SWITCH(

    TRUE(),

    SELECTEDVALUE('Maxtrix'[Header#]) = Total Positive,

    CALCULATE(COUNTROWS('Table1'), 'Table1'[Count Sum]>0), **This Works**

    SELECTEDVALUE('Maxtrix'[Header#]) = Total Negative,

    CALCULATE(COUNTROWS('Table1'), 'Table1'[Count Sum]<0), **This Works**

    SELECTEDVALUE('Maxtrix'[Header#]) = Positive to Negative,

    CALCULATE(COUNTROWS('Table1'), 'Table1'[Date]=MAXD && 'Table1'[Count Sum]>0, 'Table1'[Date]=PREVD && 'Table1'[Count Sum]<0 ),

    SELECTEDVALUE('Maxtrix'[Header#]) = Negative to Positive,

    CALCULATE(COUNTROWS('Table1'), 'Table1'[Date]=MAXD && 'Table1'[Count Sum]<0, 'Table1'[Date]=PREVD && 'Table1'[Count Sum]>0 ),

)

Thank you in advance for the help!

[ThxAlot · Accepted solution]

[jhollingworth]

Hi ThxAlot,

This worked! Thank you for the help.

I do have a question about the measure you created [SUM Count Sum]. Why can't you use "SUM('Data'[Count Sum])" instead? I tried this and it didn't work, but when I created a measure like you did, it did work.

Thank you!

[lbendlin]

I don't see two negatives for 4/1/2024

Please clarify.

[jhollingworth]

That was an error from me changing data to make a better example of different scenerios. ThxAlot answer it though.