Get previous row value in new column

[Anonymous]

Hi,

I have seen all the solutions related to this topic and couldn't find a solution to my problem hence posting here.  I have created a new table in power bi using summarize which is just a count of users grouped by date.  I am expecting as below

In PowerBI, I tried adding a column with DAX formula as below 

Previous = CALCULATE(MAX(ddd[Users]),FILTER(ddd,EARLIER(ddd[Date]) > ddd[Date] ))  and what I get is as below:  It looks like it is taking max of previous values may be because MAX is used in the formula, but I couldn't find any other option to get this done!  Am missing something here.  Can anyone help on this please..

[Greg_Deckler]

You need to FILTER the table as you are doing but then get the MAXX of your date of those filtered rows and then return the value for that row. See my article on Mean Time Before Failure (MTBF) which uses EARLIER in this way: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[Anonymous]

Thanks.. I visited the link and I get your point on getting the Max previous date first and then fetching corresponding value but how to do that in a single DAX formula, still struggling.  If you can help.. I tried something like below but it gave all blank values.

Previous = var PreviousDate = CALCULATE(MAX(ddd[Date]),FILTER(ddd,EARLIER(ddd[Date]) > ddd[Date] ))

Return CALCULATE(FIRSTNONBLANK(ddd[NewUsers],""),ddd[Date] = PreviousDate)

[Greg_Deckler · Accepted solution]

Sample data would help but perhaps something like:

Previous = 
var PreviousDate = MAXX(FILTER(ALL(ddd),EARLIER(ddd[Date]) > ddd[Date] ),ddd[Date])

Return CALCULATE(MAX(ddd[NewUsers]),FILTER(ddd,ddd[Date] = PreviousDate))

[montoyesco]

That was an elegant solution Greg. I was wondering if you could help me with this another topic..... please!

https://community.powerbi.com/t5/DAX-Commands-and-Tips/LOOKUPVALUE-overrides-RLS/td-p/1195715

[Anonymous]

Thanks.  works now!  I was close but didn't get what was missing from mine one.  I have used MAX instead of MAXX and not used FILTER explicitly in return...

Previous = var PreviousDate = CALCULATE(MAX(ddd[Date]),FILTER(ddd,EARLIER(ddd[Date]) > ddd[Date] ))

Return CALCULATE(MAX(ddd[NewUsers]),ddd[Date] = PreviousDate)