topic Re: My use of EARLIER the determine a prior value is not working in DAX Commands and Tips

My use of EARLIER the determine a prior value is not working

tslyonsnj — Thu, 05 Mar 2020 04:37:52 GMT

I'm trying to develop a pct change in a value row to row, but am stuck getting the prior value of my field. I've looked at many of the similar threads in this community, and cannot identify where my code is not matching those examples. I'd appreciate having a more experienced set of eyes on it, if someone's willing. Thanks.

I'm interested in the % change in field "Tot", which is the sum of Columns "At Minimum Wage" and "Below Minimum Wage".

Here's my code to capture the prior value of "Tot":

PrevRowTot =

CALCULATE(

MAX('Minimum Wage'[Tot]),

FILTER('Minimum Wage','Minimum Wage'[Year]=EARLIER('Minimum Wage'[PrevYear]))

I've tried SUM, MIN, and MAX to see if I can determine any pattern in the resultant change in the values returned, but to no avail, and none of them match the prior value of "Tot".

Being kind of new, I'm not sure how to share my pbix, but will be happy to folllow instruction. Thanks once again.

Re: My use of EARLIER the determine a prior value is not working

Greg_Deckler — Thu, 05 Mar 2020 04:51:02 GMT

Best way is to put your PBIX on OneDrive or Box and share. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: My use of EARLIER the determine a prior value is not working

Anonymous — Thu, 05 Mar 2020 12:13:02 GMT

Hi Greg_Deckler.

It's a problem with EARLIER. You should be able to help him :))) I can't understand why it does not work. After all, you say that...

"EARLIER is the greatest DAX function of all time. If you can't solve your calculation problem, just use EARLIER. Works every time.".

Why then does it not work if you say that it works every time, Greg?

Best
D

Re: My use of EARLIER the determine a prior value is not working

tslyonsnj — Fri, 06 Mar 2020 03:21:56 GMT

Thanks for responding. I will read your referenced post but have saved my pbix on a shared OneDrive folder:

https://1drv.ms/u/s!AlUz3UOkp9Gi83jtVmkUgaHfta-D?e=wQVnUg

Thanks again.

Re: My use of EARLIER the determine a prior value is not working

Greg_Deckler — Fri, 06 Mar 2020 03:38:33 GMT

As a matter of fact Anonymous ...

tslyonsnj - Your use of EARLIER was brilliant! You just need MORE EARLIER!!

PrevRowTot = CALCULATE( MAX('Minimum Wage'[Tot]), FILTER('Minimum Wage','Minimum Wage'[Year]=EARLIER('Minimum Wage'[PrevYear]) && [State]=EARLIER('Minimum Wage'[State])) )

Re: My use of EARLIER the determine a prior value is not working

tslyonsnj — Sat, 07 Mar 2020 04:03:03 GMT

aaahhh..hahaha.... thanks so much. As soon as I saw your response it all fell into place. I'm learning to think in terms of filters, but hadn't thought through the impact of State as an additional dimension. I had been wondering what I could have done better from a troubleshooting perspective, and the answer is to verify that I've considered all potential contributors to the filter context.

Thanks again Greg_Deckler !!

btw... one of life's pleasant little ironies that the solution did indeed involve more EARLIER... 🙂

Re: My use of EARLIER the determine a prior value is not working

Greg_Deckler — Sat, 07 Mar 2020 04:04:39 GMT

No worries, DAX is easy to learn but difficult to master. Takes a while to learn how to "Think DAX"!! 🙂