Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi,@adnane_k 

     

    You can try the following methods.

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

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous So:

Start date = [Date]

End date = 
  VAR __Value = [New Value]
RETURN
  MINX(FILTER(ALL('Table'),[Old Value] = __Value),[Date]) - 1