Forum Discussion

New_be's avatar
New_be
Helper V
6 years ago
Solved

Read previous rows from dates

Hi everyone! Im still new in power bi. Currently working on this project where i need to show the date for yesterday. For example in the image, if my date is 22/5/2020 (friday), i need to get the date for yesterday. But if my previous date have an off date, i need to get the value from all the off dates & from the day before it. Example if my date is 1/6/2020 (Monday), i need to get dates that takes from 29/5 until 31/5.  The logic is it will automatically count the OFF, and also take the previous date from the last OFF date.

I have 2 columns, DATE & DATE_OFF. Kindly need advice & ideas from the experts.

  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    New_be 

     

    Try this

    Column =
VAR _PreviousDay =
    MAXX (
        FILTER (
            MyTable,
            MyTable[Date]
                < EARLIER ( MyTable[Date] )
                && MyTable[Status] <> "OFF"
        ),
        MyTable[Date]
    )
VAR _result =
    SUMX (
        FILTER (
            MyTable,
            MyTable[Date]
                < EARLIER ( MyTable[Date] )
                && MyTable[Date] >= _PreviousDay
        ),
        MyTable[Cost]
    )
RETURN
    _result



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

7 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    New_be 

     

    DateDayStatus
    19-05-2020Tuesday 
    20-05-2020Wednesday 
    21-05-2020Thursday 
    22-05-2020Friday 
    23-05-2020SaturdayOFF
    24-05-2020SundayOFF
    25-05-2020MondayOFF
    26-05-2020TuesdayOFF
    27-05-2020Wednesday 
    28-05-2020Thursday 
    29-05-2020Friday 
    30-05-2020SaturdayOFF
    31-05-2020SundayOFF
    01-06-2020Monday 
    02-06-2020Tuesday 
    03-06-2020Wednesday 
    04-06-2020Thursday 

     

    Create a calculated column

     

    Previous Working Day = MAXX(FILTER(MyTable,MyTable[Date]<EARLIER(MyTable[Date])&&MyTable[Status]<>"OFF"),MyTable[Date])

     



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

     

    • New_be's avatar
      New_be
      Helper V
      6 years ago

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      A vey good solution! But what i mean is if the date is 1/6/2020 (monday), and we have 2 days off, we will take the data that add from date 29-31 May 2020. How to handle with it ?

      nandukrishnavs 
      amitchandak 

       

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    New_be , Two new columns like that.

     

    Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date]) //OR
Work Date = if([Status]="OFF",BLANK(),[Date]) 
Last working date = maxx(filter('Date','Date'[Work Date]<EARLIER('Date'[Work Date])),'Date'[Work Date])