cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Show increase or decrease from the previous row date/value (or second last),

Hi.
I have survey input where customers submit the most recent count of a required value.  I'm able to filter to the most recent submissions using MAX to get proper numbers, which is good. 

 

What I would like to show (in a report table) is the increase or decrease from the previous row date/value (or second last), if the value exisits (which it doesn't for some).  If no submission exisits I would like to show just the increase/decrease, and nothing for values that don't have a previous submission.  I've tried a bunch of measures but i'm not getting it.

 

To get the previous date value, I tried this combo of measures (which worked, but not sure if it's best):

measure1 = CALCULATE(MAX('data'[Start time]),
FILTER('data','data'[Start time] <>MAX('data'[Start time])
))
Last Submission Value =
var secondlast = [measure1]
return
CALCULATE(SUM('data'[submittedValue]),'data'[Start time] = secondlast)
 
 Capture.JPG
However, when I subtract the second value from the most recent value, the increase or decrease is correct for only the submissions that had a previous value.  The submissions that didn't have a value, the most recent value shows up and looks weird.  Same goes for calculating the percent change.   100% shows up for those that didn't not have a previous value. 
 

This is basically the format of the incoming background form data table. 

 
LocationSubmissionDateValue
Shelter AOctober 23, 20205
Shelter BOctober 23, 20206
Shelter ZOctober 24, 20203
Shelter ZOctober 22, 20204

 

Any help would be great, but i'm ok if this is just not practical.

 

1 ACCEPTED SOLUTION
Solution Specialist
Solution Specialist

Hi, @mcinnisbr ,

 

I think I understand what you mean.

The key to this is to position the second largest date for each location in the dataset.

Suppose we have the following data:

 

20201028174721.png

 

Then you need to create a calculated column (for example: [ID]) to mark the second largest date:

 

 

ID = 
VAR _D =
    CALCULATE (
        MAX ( 'Sheet1'[SubmissionDate] ),
        ALLEXCEPT ( 'Sheet1', 'Sheet1'[Location] )
    )
VAR _LD =
    CALCULATE (
        MAX ( Sheet1[SubmissionDate] ),
        FILTER ( ALL ( Sheet1 ), 'Sheet1'[SubmissionDate] < _D )
    )
RETURN
    SWITCH ( 'Sheet1'[SubmissionDate], _D, 1, _LD, 2, BLANK () )

 

Then you can create measures for both [MOST RECENT SUBMISSION] and [LAST SUBMISSSION], so that you can get the change for that:

 

 

CHANGE = 
VAR _THIS = [MOST RECENT]
VAR _LAST = [LAST]
RETURN
    DIVIDE ( _THIS - _LAST, _LAST, BLANK () )

 

 

 The results are shown in the below figure, it works perfectly:

 

20201028175340.png

 

Mark this post as solution if this helps,thanks!

( See Davis.Z's blog at LinkedIn)

View solution in original post

4 REPLIES 4
Solution Specialist
Solution Specialist

Hi, @mcinnisbr ,

 

I think I understand what you mean.

The key to this is to position the second largest date for each location in the dataset.

Suppose we have the following data:

 

20201028174721.png

 

Then you need to create a calculated column (for example: [ID]) to mark the second largest date:

 

 

ID = 
VAR _D =
    CALCULATE (
        MAX ( 'Sheet1'[SubmissionDate] ),
        ALLEXCEPT ( 'Sheet1', 'Sheet1'[Location] )
    )
VAR _LD =
    CALCULATE (
        MAX ( Sheet1[SubmissionDate] ),
        FILTER ( ALL ( Sheet1 ), 'Sheet1'[SubmissionDate] < _D )
    )
RETURN
    SWITCH ( 'Sheet1'[SubmissionDate], _D, 1, _LD, 2, BLANK () )

 

Then you can create measures for both [MOST RECENT SUBMISSION] and [LAST SUBMISSSION], so that you can get the change for that:

 

 

CHANGE = 
VAR _THIS = [MOST RECENT]
VAR _LAST = [LAST]
RETURN
    DIVIDE ( _THIS - _LAST, _LAST, BLANK () )

 

 

 The results are shown in the below figure, it works perfectly:

 

20201028175340.png

 

Mark this post as solution if this helps,thanks!

( See Davis.Z's blog at LinkedIn)

View solution in original post

DavisBI, 

Many apologies, but i'm just getting to this.  This works very well and way more elegantly than my previous version for percentage.   Is it possible to show the raw change value as 'blank' if there are no previous dates to subtract from?  My version works, but subtracting the the recent from the previous gives me the only value regardless.  if not possible, that's ok.

 

Capture.PNG

 

Thanks. 

Helper II
Helper II

@mcinnisbr  Hi mcinnisbr, I might have misunderstood your question, if what you want is not to show any value for [Change from last report] and [diffMax2ndLast] in case there is no Last submission, I guess we can solve the problem by adding a condition such as  LastSubmissionValue >0 when we calculate these meaures.
e.g.) [diffMax2ndLast] = calculate( MostrecentSubmissionValue - LastSubmissionValue, LastSubmissionValue <> blank()  )

 

 

 

Thanks for the reply.  That didnt quite work.  I'm probably not explaining things well either.  I guess what i'm trying to do is almost like a 'change from last report' like you see for COVID-19 case tables, but by customer instead of by date like you see in the covid screen below.  

 

mcinnisbr_0-1603827152977.png

 

My raw table (forms submissions) looks like this.  I want to be able to subtract the last date from the previous and display the change in a report similar to my first post.  And, display + or - value by client, not by date.   
Location SubmissionDate         Value   
Shelter A   October 23, 2020    5             
Shelter B   October 23, 2020    6                
Shelter Z   October 24, 2020    3
Shelter Z   October 22, 2020    4

 

If this is too confusing i'll have to try again another time.   Thanks.

 

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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