Forum Discussion
Need help with logging date and information
Hi ps92 ,
I suggest you to add an [Index] column in your table. Then create a measure.
Measure = VAR _STEP1 = ADDCOLUMNS ( ALL ( 'Table' ), "Last0Date", IF ( 'Table'[Below Target] = 0, BLANK (), CALCULATE ( MIN ( 'Table'[Date] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Section] ), 'Table'[Date] > EARLIER ( 'Table'[Date] ) && 'Table'[Below Target] = 0 ) ) ) ) VAR _STEP2 = ADDCOLUMNS ( _STEP1, "Running Total", SUMX ( FILTER ( _STEP1, [Date] <= EARLIER ( [Date] ) && [Section] = EARLIER ( [Section] ) && [Last0Date] = EARLIER ( [Last0Date] ) ), [Below Target] ) ) RETURN MAXX ( FILTER ( _STEP2, [Index] = MAX ( 'Table'[Index] ) ), [Running Total] )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello parry2k , thank you for the response. To elaborate more, I have a date column, a line number(a categorical column) and the binary output based on a certain condition. I want to raise a flag if the output is 1 for two consecutive days. And I also want to log the latest date of the consecutive days. If it is 7 consecutive days, I want to raise a different flag, and log the date again. But the flags should be for the categorical column. I want to be able to see when the flags were raised. The meain reason is to monitor the date of consecutive occurences and consecutive 5 occurences. The measure column can be used to find the date and consecutive days. Happy to provide more details if needed. Thanks!!
| Date | Section | Below Target | Measure |
| 01-Nov-23 | Line 1 | 1 | 1 |
| 02-Nov-23 | Line 1 | 0 | 0 |
| 03-Nov-23 | Line 1 | 0 | 0 |
| 06-Nov-23 | Line 1 | 1 | 1 |
| 07-Nov-23 | Line 1 | 1 | 2 |
| 08-Nov-23 | Line 1 | 0 | 0 |
| 09-Nov-23 | Line 1 | 1 | 1 |
| 10-Nov-23 | Line 1 | 0 | 0 |
| 01-Nov-23 | Line 2 | 1 | 1 |
| 02-Nov-23 | Line 2 | 0 | 0 |
| 03-Nov-23 | Line 2 | 0 | 0 |
| 06-Nov-23 | Line 2 | 1 | 1 |
| 07-Nov-23 | Line 2 | 1 | 2 |
| 08-Nov-23 | Line 2 | 1 | 3 |
| 09-Nov-23 | Line 2 | 1 | 4 |
| 10-Nov-23 | Line 2 | 0 | 0 |
Hi ps92 ,
I suggest you to add an [Index] column in your table. Then create a measure.
Measure =
VAR _STEP1 =
ADDCOLUMNS (
ALL ( 'Table' ),
"Last0Date",
IF (
'Table'[Below Target] = 0,
BLANK (),
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Section] ),
'Table'[Date] > EARLIER ( 'Table'[Date] )
&& 'Table'[Below Target] = 0
)
)
)
)
VAR _STEP2 =
ADDCOLUMNS (
_STEP1,
"Running Total",
SUMX (
FILTER (
_STEP1,
[Date] <= EARLIER ( [Date] )
&& [Section] = EARLIER ( [Section] )
&& [Last0Date] = EARLIER ( [Last0Date] )
),
[Below Target]
)
)
RETURN
MAXX ( FILTER ( _STEP2, [Index] = MAX ( 'Table'[Index] ) ), [Running Total] )Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Rico,
This actually worked to solve the problem I was facing.
Is there a way to be able to display the date of 2 occurences, and only show the date of the next 2 occurences after a certain time frame?For example, a measure that gives me the date of first 2 occurences,i.e 7-11-2023, but if there is 2 occurences in the next 2 weeks, the measure wont give me that date, it will only gicve me the next 2 consecutive occurences that take place after 2 weeks.
I'll try to solve it myself as well. But thank you for the above solution. 🙂