Forum Discussion
Get previous row value in new column
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..
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))
5 Replies
- Greg_Deckler
Community Champion
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/339586
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
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))