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 |
I want to have the following table :
| Name | Value | Start Date | End Date |
| A | 1 | 01/05/2020 | 03/05/2020 |
| A | 2 | 04/05/2020 | 04/05/2020 |
| A | 3 | 05/05/2020 | NULL |
And then be able to create a graph where I can track the COUNT of [Values] each day.
Excel File for better view: https://www.mediafire.com/file/thbbx189bfb3fj3/Nouveau_Feuille_de_calcul_Microsoft_Excel_%25282%2529.xlsx/file
Thanks in advance.
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.
4 Replies
- Greg_Deckler
Community Champion
Anonymous where does the 3/5/2020 come from? And why does the 2 get a 4/5/2020 end date while 3 gets null?
- Greg_Deckler
Anonymous So:
Start date = [Date] End date = VAR __Value = [New Value] RETURN MINX(FILTER(ALL('Table'),[Old Value] = __Value),[Date]) - 1
v-zhangtiCommunity Support
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.
