Read previous rows from dates

[New_be]

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.

[amitchandak]

@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])

[nandukrishnavs]

@New_be 

Date	Day	Status
19-05-2020	Tuesday
20-05-2020	Wednesday
21-05-2020	Thursday
22-05-2020	Friday
23-05-2020	Saturday	OFF
24-05-2020	Sunday	OFF
25-05-2020	Monday	OFF
26-05-2020	Tuesday	OFF
27-05-2020	Wednesday
28-05-2020	Thursday
29-05-2020	Friday
30-05-2020	Saturday	OFF
31-05-2020	Sunday	OFF
01-06-2020	Monday
02-06-2020	Tuesday
03-06-2020	Wednesday
04-06-2020	Thursday

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]

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 

 

[nandukrishnavs]

@New_be did you check my original post?

[New_be]

@nandukrishnavs i do check your solution and it is awesome! But i think i wrongly asked before. Pardon for that. My question is how can i get previous data like in this image that i draw. For example, if the date 27/5/2020 (wednesday) and we have 4 days off from 23-26 May 2020, then i want to take the data from before the last date off that is from 22-26 May 2020. And then the value for 27/5/2020 (wed) will b e the sum of cost from22-26 May 2020.

I dont know whether it is possible to do that in power bi, but i hope with your help, i can learn something new 🤗

[nandukrishnavs · Accepted solution]

@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 🙂

[New_be]

can i change my(cost) column in your code into a measure? i mean, can i use measure instead of column?