Forum Discussion

Musadev's avatar
Musadev
Resolver III
2 years ago

You will have multiple dates for a single ID right?
right now you have only 3 but in future, it will have multiples

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Yes, In future new records will add for same ID.

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , We can use offset function to get that

     

    Last row = CALCULATE(Sum(Table[Numer of Days]) , OFFSET(-1, ALLSELECTED('Table'[ID],'Table'[DAte]), ORDERBY('Table'[Date],asc),KEEP,PARTITIONBY('Table'[ID])))+0

     

    Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks amitchandak  for sharing above logic,
      but I want to confirm you that I am expecting to create NO of days column. as of now I dont have that column in my table for understanding i just mentioned there.
      Sorry for unclear information.

      • amitchandak's avatar
        amitchandak
        Super User
        2 years ago

        Anonymous , My Mistake ,Please try

         

        Last row =
        var _last =CALCULATE(Max(Table[Date]) , OFFSET(-1, ALLSELECTED('Table'[ID],'Table'[DAte]), ORDERBY('Table'[Date],asc),KEEP,PARTITIONBY('Table'[ID]))), Max('Table'[Date])
        return
        if(isblank(_last), 0, Datediff(_last, Max('Table'[Date]) , day))