Forum Discussion
Need Help with DAX expression - Opportunity Pipeline - Point in Time
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
Anonymous , refer if this can help
10 Replies
- AntrikshSharma
Community Champion
Upload it to google drive / one drive / drop box and share the linkThanks for the suggestion AntrikshSharma . Have uploaded to google drive and link provided
https://drive.google.com/file/d/13dHaYfjNFm4xUqbJL9BLDl4xOD-yTR3Z/view?usp=sharing
- AntrikshSharmaFound 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
- amitchandak
Super User
Anonymous , refer if this can help
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))
