Need Help with DAX expression - Opportunity Pipeline - Point in Time

[Anonymous]

Hi

I am new to PowerBI. Stuck and need help!!

We have Opportunities pipeline data in the following format

Opportunity	GrossBooking	Stage1Date	Stage2Date	Stage3Date	CurrentStage
OP1	100	12/15/2019	2/10/2020		Stage2
OP2	150	12/1/2019			Stage1
OP3	200	2/1/2020	3/15/2020	6/1/2020	Stage3
OP4	250	11/1/2019	1/15/2020	1/20/2020	Stage3
OP5	300	12/15/2019	6/1/2019		Stage2
OP6	350	1/1/2020	2/1/2020	3/1/2020	Stage3

StageDate fields capture the date on which an opportunity entered that stage.

I am trying to create DAX expression for stages (stage1, stage2,stage3.. etc), to use on a Matrix  to show which opportunity was in which stage during a time period.

Generate outputs as below based on Month \ Quarter \ Year. When we run for Quarter\year, it should not just aggregate monthly numbers. Instead, identify opportunities that are in that particual stage at least some point in that Quarter and get sum of gross booking 

	Jan	Feb	Mar
Stage1	900	750	650
Stage2	700	450	300

Or

	Q1	Q2
Stage1	1350	450
Stage2	900	600

Here is the link to the pbix file

https://drive.google.com/file/d/13dHaYfjNFm4xUqbJL9BLDl4xOD-yTR3Z/view?usp=sharing

Any help is appreciated

Thanks

[amitchandak · Accepted solution]

@Anonymous , refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

@amitchandak  Thanks for the reference. Based on your reference I was able to create a DAX expression like below which worked. Thanks a bunch for your help.. 

I should have joined this community sooner.

Stage1 = CALCULATE

(

SUMX(

FILTER

(Opportunities,

Opportunities[Stage1Date] <= MAX('Calendar'[Date]) &&

(ISBLANK(Opportunities[Stage2Date])

|| (Opportunities[Stage2Date] > Max('Calendar'[Date]))

|| (

Opportunities[Stage2Date] >= Min('Calendar'[Date]) &&

Opportunities[Stage2Date] <= max('Calendar'[Date])

)

)

),Opportunities[GrossBooking]

),CROSSFILTER(Opportunities[Stage1Date],'Calendar'[Date],None)

)

[AntrikshSharma]

Upload it to google drive / one drive / drop box and share the link

[Anonymous]

Thanks for the suggestion @AntrikshSharma . Have uploaded to google drive and link provided

https://drive.google.com/file/d/13dHaYfjNFm4xUqbJL9BLDl4xOD-yTR3Z/view?usp=sharing

[AntrikshSharma]

Found some data modelling issues and fixed that, see if it works for you now. Remember always make your tables tall rather than wide.

https://drive.google.com/file/d/13dHaYfjNFm4xUqbJL9BLDl4xOD-yTR3Z/view?usp=sharing

[Anonymous]

@AntrikshSharma  Can you try please uploading the modified file one more time? I am not able to see any changes to the file I uploaded.

Thanks again for your help.

George Ponnatt

[AntrikshSharma]

That was strange! use this:
https://drive.google.com/file/d/1RRFPHf6Y3yq87jGJnec-Vgo9fWcuHy1p/view?usp=sharing

[Anonymous]

@AntrikshSharma  Unfortunately the calculation below is wrong. It is not a straigtforward SUM()

For example: During Jan 2020, the followings Opps are in Stage 1 : OP1, OP2, OP4, OP5,OP6

Take OP1; it entered Stage1 Date on 12/15/2019. But it continues in that Stage till 2/10/2020, till it enters Stage2. So OP1 is in Stage1 on Jan 2020.

So Stage 1 - Jan would be 900.  And Stage 3 -Jan should be 250. In Jan, only OP4 was in Stage 3.

ie.. Stage dates show what date the opportunity entered a stage, and it remains in that stage till it enters Next stage.

Appreciate the attempt.

[AntrikshSharma]

Can you define the lower boundaries and upper boundaries of each stages, maybe that could help, worth trying.

[Anonymous]

@AntrikshSharma  Stage boundaries of each opportunity is implied within the above opportunity data itself. Each opportunity starts at Stage1, then moves on to Stage 2 and so on.

Ex:

OP1  (Current Stage is Stage2)

Stage1 12/15/2019 - 2/9/2020

Stage2  2/10/2020  - Current

OP2 (Current Stage isStage1)

Stage1    12/1/2019  - Current

OP3 (Current stage is Stage3)

Stage1  2/1/2020 - 3/14/2020

Stage2   3/15/2020 - 6/1/2020

Stage3   6/1/2020  - Current