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 guys,
So I have a table called OppStageChange withthe columns as below, it tracks the stage an opporutnity is at
Opportunity - createdonutc - StageNumber
ABC 1/1/2017 3
ABC 2/15/2017 4
ABC 3/1/2017 1
EFG 1/1/2017 3
EFG 2/1/2017 4
XYZ 1/1/2017 1
XYZ 2/15/2017 5
What I need is to show a count of all opportunities that are between the stage of 3 and 5 at the last time they were modified, the above would be filtered to below
ABC 3/1/2017 1
EFG 2/1/2017 4
XYZ 2/15/2017 5
Since there are two opportunities between 3 and 5, the result would be "2". I need this to be a calculated measure without the addition of any calculated columns please.
Moiz
Hi @moizsherwani,
You can use below formula to create a new table with the result which you want:
Result = SUMMARIZE('sample file',[Opportunity],"createdonutc",MAX('sample file'[createdonutc]),"StageNumber",LOOKUPVALUE('sample file'[StageNumber],'sample file'[Opportunity],[Opportunity],'sample file'[createdonutc],MAX('sample file'[createdonutc])))
Regards,
Xiaoxin Sheng
Hello @moizsherwani,
I would recommend taking this custom column and then making it to the measre .
Custom coulmn would go as : = if stage between 3 and 5 then name
Ankit, that would not work, remember I need the last date for each opportunity and not just stage between 3 and 5. Also since there is a LOT of data I do not want to create a custom column as I am certain there is a way around using a measure and LASTNONBLANK
I would use either of these two measures:
Count of Opportunities with Latest Stage between 3-5 = COUNTROWS ( FILTER ( VALUES ( OppStageChange[Opportunity] ), VAR LatestStage = CALCULATE ( MAX ( OppStageChange[StageNumber] ), LASTDATE ( OppStageChange[createdonutc] ) ) RETURN AND ( LatestStage >= 3, LatestStage <= 5 ) ) ) Count of Opportunities with Latest Stage between 3-5 v2 = CALCULATE ( DISTINCTCOUNT ( OppStageChange[Opportunity] ), FILTER ( VALUES ( OppStageChange[Opportunity] ), VAR LatestStage = CALCULATE ( MAX ( OppStageChange[StageNumber] ), LASTDATE ( OppStageChange[createdonutc] ) ) RETURN AND ( LatestStage >= 3, LatestStage <= 5 ) ) )
The MAX is just there to take the maximum StageNumber if there are ties on the latest date.
The second measure can be adapted to more general calcs by changing DISTINCTCOUNT to something else.
More generally, you could use a Dynamic Segmentation approach if you want to be able to look at more general groupings of Latest Stage.
Cheers,
Owen
@OwenAuger. This is the error when using the formula btw
MdxScript(Model) (374, 17) Calculation error in measure 'OppStageChange'[CountClosePQ]: A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.
Ah right - had forgotten LASTDATE doesn't like duplicates (can happen when you aren't using a separate calendar table).
Try changing
LASTDATE ( OppStageChange[createdonutc] )
to
LASTNONBLANK ( OppStageChange[createdonutc], 0 )
Thanks so much, what if the stage numbers were text instead of number so "one", "two", "three", in this case the MAX could not be used in case there was a tie, would we used LASTNONBLANK then?
Hi @moizsherwani,
Yes, you can use lastnonblank to instead, but you'd better calculate at var function.
Regards,
Xiaoxin Sheng
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |