Need help with logging date and information

[ps92]

Hello all,

I have a date column, a category column and a value column. I want to be able to sum the value column with the previous column only, and if the sum is 2 (the value can be 1 or 0 for the column) for the given category, then it should log the earlier date of the two. I am having trouble figuring out the logic. Would appreciate the support.

[ps92]

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

[Anonymous · Accepted solution]

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.

[ps92]

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. 🙂

[parry2k]

@ps92 can you provide the sample data and the expected output? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490