topic Show increase or decrease from the previous row date/value (or second last), in DAX Commands and Tips

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

mcinnisbr — Tue, 27 Oct 2020 14:46:47 GMT

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)

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.

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

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

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

colacan — Tue, 27 Oct 2020 18:13:42 GMT

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() )

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

mcinnisbr — Tue, 27 Oct 2020 19:40:38 GMT

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.

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.

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

DavisBI — Wed, 28 Oct 2020 09:57:50 GMT

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:

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:

Mark this post as solution if this helps,thanks!

( See Davis.Z's blog at LinkedIn)

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

mcinnisbr — Tue, 03 Nov 2020 17:11:31 GMT

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.

Thanks.