Calculate Values based on specific dates

[EvertonRosa]

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

[lbendlin]

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.

[EvertonRosa · Accepted solution]

Thanks. I did find a solution.