Forum Discussion
Calculate Values based on specific dates
Community, I need a little help here.
I have a table like the one bellow and I want to define 2 measures:
1) Value of Estimated opportunities, considering first day of each month of the year.
In this case, how to consider opportunities that were set to status 'Won' during the month as 'Open' on the first day of the month (past time intelligence)?. Ex: Value of All opportunities, on 01/07/2027, based on 'EstimatedClose' date.
2) Value of Won opportunities, considering last day of each month of the year, based on 'ActualClose" & that EstimatedClose same month.
| idOpportunity | Createdon | EstimatedClose | ActualClose | Status | Value |
| 1 | 01/07/2021 | 22/07/2021 | Open | 1.000,00 | |
| 2 | 01/07/2021 | 22/07/2021 | 22/07/2021 | Won | 1.000,00 |
| 3 | 01/07/2021 | 22/07/2021 | 22/07/2021 | Won | 1.000,00 |
| 4 | 01/07/2021 | 22/07/2021 | Open | 1.000,00 | |
| 5 | 01/07/2021 | 22/07/2021 | Lost | 1.000,00 |
Thanks. I did find a solution.
2 Replies
You will want to use a Dates table and use its [date] column for your visuals. Once you have that you can create all your measures as needed.
Thanks. I did find a solution.
