Forum Discussion
Need help with logging date and information
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.
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.
4 Replies
- parry2k
Super User
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 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 ZhouIf 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. 🙂
