Forum Discussion
Create a StartDate and EndDate column after value change
Hello, I hope you're doing well and in good health ! I have a table as follows : Date Name Old Value New Value 01/05/2020 A 1 04/05/2020 A 1 2 05/05/2020 A 2 3 ...
Hi,@adnane_k
You can try the following methods.
- Create a calculated column and use the if function to judge whether the value has changed.
Value = IF ( 'Table'[Old Value] = 'Table'[New Value], 'Table'[Old Value], 'Table'[New Value] )2. Create a calculation column to calculate the Start Date and End Date.
Start Date = IF ( 'Table'[Old Value] <> 'Table'[New Value], [Date], BLANK () )End Date = MINX ( FILTER ( 'Table', 'Table'[Old Value] = EARLIER ( 'Table'[Value] ) ), [Start Date] - 1 )3. About tracking the COUNT of [values] each day, see if I understand correctly.
Count = CALCULATE ( COUNT ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[Start Date] = MAX ( 'Table'[Start Date] ) ) )Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more.
Greg_Deckler
Community Champion
Community ChampionAnonymous where does the 3/5/2020 come from? And why does the 2 get a 4/5/2020 end date while 3 gets null?
Hello, you can consider the end date as 04/05 instead of 03/05 but i don't want Step1 to be counted on the 4th day since it'll move to Step2. As for the null since Step3 is the most recent Value there is no end date (yet)
- Greg_Deckler
Anonymous So:
Start date = [Date] End date = VAR __Value = [New Value] RETURN MINX(FILTER(ALL('Table'),[Old Value] = __Value),[Date]) - 1