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 there,
I've already looked around the forum for a similar issue, and even though I found some that are somewhat like mine, it didn't help me solve it. So here goes.
I'm tracking Stages in the sales process, and every time a Stage changes in Salesforce, I want to calculate the new value of this Stage (each stage has a different percentage associated with it).
As a result I want to show the value on each day/week for all open opportunities, but only for the latest Stage. So whenever a Stage changes from Negotiation to Closed Won for instance, I only want to see the value at the Closed Won row, since the Negotiation row would no longer be relevant/valid.
Currently I use a measure that gives me this as a result:
It is updating the values every time a Stage change occurs, however it is summing up the total and thus also including the previous Stages. Instead of only looking at the latest Stage.
What I would want to see in the above table, is only the value for the latest Stage, but that value has to remain valid for future dates, unless the Stage changes again.
I use a date table for the dates. The measure that I used to generate the above result:
To be completely clear I have attached a screenshot of the result I'm after for the specific OpportunityID that is selected in the pbix file.
@PowerBI_RD , Assume created date and date(only date) column, then sum of latest is
Last Qty = Var _max = minx(filter( ALLSELECTED(Data1), Data1[Date] = max(Data1[Date]) ),Data1[Created Date])
return
CALCULATE(countdistinct(Data1[qty]), filter( (Data1), Data1[Date] = max(Data1[Date]) && Data1[Created Date] =_max))
Sum Last Qty = Sumx(VALUES(Data1[Date]) , [Last Qty])
On Sum Last qty you can use datesytd , but use a date table in such a case
calculate([Sum Last Qty], datesytd('Date'[Date]) )
Thanks for your response Amit. I found your post about this, but there you used sum instead of distinctcount:
Last Qty = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[ID] = max(Data1[ID]) ),Data1[Date])
return
CALCULATE(sum(Data1[qty]), filter( (Data1), Data1[ID] = max(Data1[ID]) && Data1[Date] =_max))Sum Last Qty = sumx(VALUES(Data1[ID]) , [Last Qty])
I have tried your latest suggestion but either I don't understand what to put where, or I'm just doing something wrong. Did you look at my dummy file?
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 |
---|---|
110 | |
109 | |
89 | |
76 | |
66 |
User | Count |
---|---|
126 | |
112 | |
99 | |
82 | |
73 |