Forum Discussion
Consecutive days with no increment in Running Total
Dear All,
I want to create a calculated column that returns a string if a Player has an increment in goal scored compared with the previuos day.
Please consider that "goal " is a running total. I cannot change the data source.
If you have an increment return "New goal", if the score is equal return "No".
Attached the table as example.
Many Thanks
Hi,
Try this calculated column formula
Column = calculate(countros(Data),filter(Data,Data[Name]=earlier(Data[Name])&&Data[Day]<=earlier(Data[Day])&&Data[Goal]=earlier(Data[Goal])))
Hope this helps.
5 Replies
- rajendraongole1
Super User
Hi Step927 - Create a new calculated column by clicking on Modeling > New column.
you can replace 'YourTable' with the name of your actual table.
Goal Status =
VAR CurrentDay = 'YourTable'[Day]
VAR PreviousGoal =
CALCULATE(
MAX('YourTable'[Goal]),
FILTER(
'YourTable',
'YourTable'[Name] = EARLIER('YourTable'[Name]) &&
'YourTable'[Day] = CurrentDay - 1
)
)
RETURN
IF('YourTable'[Goal] > PreviousGoal, "New goal", "No") Hi rajen,
thank you it works very well!
In addiction, which will be the best way to return the count of consecutive giornata with "No".
Looking the table I attached, for the day 7 the correct value would be 0.
For the day 15 the correct value would be 3,
For the day 16 the correct value would be 4
Fo the day 17 the count restart from 0.Do you suggest a measure or a new calculated column?
Many Thanks- Ashish_Mathur
Hi,
Try this calculated column formula
Column = calculate(countros(Data),filter(Data,Data[Name]=earlier(Data[Name])&&Data[Day]<=earlier(Data[Day])&&Data[Goal]=earlier(Data[Goal])))
Hope this helps.
It works correctly!! Many Thanks
