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 t...
  • 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     🙂

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 

 

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    New_be did you check my original post?

    • New_be's avatar
      New_be
      Helper V
      6 years ago

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      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's avatar
        nandukrishnavs
        Community Champion
        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         🙂