Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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..
Solved! Go to 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))
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...
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)
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))
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
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |