Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
moizsherwani
Continued Contributor
Continued Contributor

Last Stage

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

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
8 REPLIES 8
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ANKITBISANI
Advocate I
Advocate I

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

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

Hi @moizsherwani

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@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.

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

@moizsherwani

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 )

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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?

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

Hi @moizsherwani,

 

Yes, you can use lastnonblank to instead, but you'd better calculate at var function.


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.