cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elouizi
Helper II
Helper II

Previous duration record

Hi,

 

I am new to Power BI. I want to calculate the previous duration record of a project. I have being trying with Earlier function. But that did not work for me. Please dont use previousYear-fucntion because the dates vary alot. this is just a simple example


Can anyone please help me?

 

Regards,

 

ElouiziCapture.JPG

 

1 ACCEPTED SOLUTION

Hi,

This calculated column works fine

=LOOKUPVALUE('Duration'[Duration],'Duration'[Date],CALCULATE(MAX('Duration'[Date]),FILTER('Duration','Duration'[Brand]=EARLIER('Duration'[Brand])&&'Duration'[Date]<EARLIER('Duration'[Date]))),'Duration'[Brand],'Duration'[Brand])

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

This calculated column formula works

=LOOKUPVALUE(Data[DurationinMinutes],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Brand]=EARLIER(Data[Brand])&&Data[Date]<EARLIER(Data[Date]))),Data[Brand],Data[Brand])

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish_Mathur,

 

My replay was to you. :).

elouizi_0-1598781566524.png

I dont know why, but it says that it cannot find the column "brand" and the "date" columns.
Is there another way I can solve this without creating a colum? Otherwise I may need to create lots of other columns

 

Thanks!

 

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

This calculated column works fine

=LOOKUPVALUE('Duration'[Duration],'Duration'[Date],CALCULATE(MAX('Duration'[Date]),FILTER('Duration','Duration'[Brand]=EARLIER('Duration'[Brand])&&'Duration'[Date]<EARLIER('Duration'[Date]))),'Duration'[Brand],'Duration'[Brand])

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Looks amazing. Is there a way I can use it as a messaure? Because I need to do other calculations, otherwise I need to create a lot of columns (which will impact the performance)

Whom are you replying to?  Did my calculated column formula work?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@elouizi - If you want to create a column, you can use EARLIER, see my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
  __Current - __Previous

 

If you want a measure then @mahoneypat 's formula should work.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

mahoneypat
Super User
Super User

Are you trying to create a new column or measure?  Here is a measure expression that should work.  Replace "Table" with your actual table name and try it in a table visual with your Date, Brand, and Duration columns.

 

LastDuration =
VAR thisdate =
    MIN ( Table[Date] )
RETURN
    CALCULATE (
        LASTNONBLANKVALUE ( Table[Date], MAX ( [Table[Duration] ) ),
        ALLEXCEPT ( Table, Table[Brand] ),
        Table[Date] < thisdate
    )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi,

 

Thank you so much for you quick response.

 

I tried the formula. But it showed me the previous dates and not the previous duration.

I changed the formula a little bit to get the duration out of it. But it did not give me the expected results.

 

Could you please explain the formula? I'm a newbie that wants to learn new things.

 

Thanks!

 

 

 

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors