cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Help with counting when comparing to previous columns

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(),
CALCULATE(COUNTROWS('Table1'), 'Table1'[Count Sum]>0), **This Works**

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

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

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

Thank you in advance for the help!

1 ACCEPTED SOLUTION
Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

4 REPLIES 4
Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Frequent Visitor

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!

Super User

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

Frequent Visitor

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