Forum Discussion

ThomasWeppler's avatar
ThomasWeppler
Icon for Impactful Individual rankImpactful Individual
4 years ago
Solved

Lookup first date with a filter

Hi power Bi community

I want to know the date where a offer has been accepted.
I have a table where I can see every date and time the status of a task has been change.
My table is called todostatehistory and look like this.


I am only interested in offers which have state id 13 so the first thing to do is to add a new colum and write
Accepted = if(stateid <> 13, blank(), **************)
When a task has state id 13 I want to find the date where it changes to something else.
in the data set it will look like a row with the same todoid, but with a diffrent stateid and date and time.
So I need to filter for the same task (todostatehistory[todoid]) 

And than return the lowest date (todostatehistory[dato]) higher than the date in the selected row.

I hope this make sense all help will be greatly appreciated.
PS: If it helps I can split the date and time colum.

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    I think the initial stateid needs to be qualified correctly

    Accepted =
IF (
    todostatehistory[stateid] <> 13,
    BLANK (),
    VAR currentDate = todostatehistory[Dato]
    VAR currentState = todostatehistory[stateid]

    VAR nextDate =
        SELECTCOLUMNS (
            CALCULATETABLE (
                TOPN ( 1, todostatehistory, todostatehistory[Dato] ),
                ALLEXCEPT ( todostatehistory, todostatehistory[todoid] ),
                todostatehistory[stateid] <> currentState,
                todostatehistory[Dato] > currentDate
            ),
            "@val", todostatehistory[Dato]
        )
    RETURN
        nextDate
)

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    Try

    Accepted =
IF (
    stateid <> 13,
    BLANK (),
    VAR currentDate = todostatehistory[Date]
    VAR currentState = todostatehistory[state]
    VAR nextDate =
        SELECTCOLUMNS (
            CALCULATETABLE (
                TOPN ( 1, todostatehistory, todostatehistory[Date] ),
                ALLEXCEPT ( todostatehistory, todostatehistory[todoid] ),
                todostatehistory[state] <> currentState,
                todostatehistory[Date] > currentDate
            ),
            "@val", todostatehistory[Date]
        )
    RETURN
        nextDate
)
    • ThomasWeppler's avatar
      ThomasWeppler
      Icon for Impactful Individual rankImpactful Individual
      4 years ago

      johnt75 
      Thanks a lot for the help. Unfortunately. I get the following error message.

       "The expression refers to multiple columns. It is not possible to convert multiple columns to a scalar value."

      Accepted =
      IF (
          stateid <> 13,
          BLANK (),
          VAR currentDate = todostatehistory[Dato]
          VAR currentState = todostatehistory[stateid]

          VAR nextDate =
              SELECTCOLUMNS (
                  CALCULATETABLE (
                      TOPN ( 1, todostatehistory, todostatehistory[Dato] ),
                      ALLEXCEPT ( todostatehistory, todostatehistory[todoid] ),
                      todostatehistory[stateid] <> currentState,
                      todostatehistory[Dato] > currentDate
                  ),
                  "@val", todostatehistory[Dato]
              )
          RETURN
              nextDate
      )

       

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        I think the initial stateid needs to be qualified correctly

        Accepted =
IF (
    todostatehistory[stateid] <> 13,
    BLANK (),
    VAR currentDate = todostatehistory[Dato]
    VAR currentState = todostatehistory[stateid]

    VAR nextDate =
        SELECTCOLUMNS (
            CALCULATETABLE (
                TOPN ( 1, todostatehistory, todostatehistory[Dato] ),
                ALLEXCEPT ( todostatehistory, todostatehistory[todoid] ),
                todostatehistory[stateid] <> currentState,
                todostatehistory[Dato] > currentDate
            ),
            "@val", todostatehistory[Dato]
        )
    RETURN
        nextDate
)