Dear community,
Despite my research these days I couldn't find a solution to the following problem:
Goal:
I would like to filter a pivot table using a dynamic date range e.g. 01.01.2019 to 31.03.2019 that shows only open opportunities (which I calculate using a dax formula) during the selected time period.
I created a pivot table and a seperate date table to link the start date and end date to the pivot table data table using the data tab (connections). Within the pivot table I put the start and end date as filters. This works all fine.
Problem:
I would need to show open deal opportunities (leads) using the table (called Query2) below within the selected date range:
Query2 table:
| ID | create time stamp | update time stamp | deal status | volume |
| 1 | 01.01.2020 | 20.02.2021 | deal | 200k |
| 2 | 01.01.2020 | 20.02.2021 | deal | 100K |
| 3 | 01.01.2020 | 20.02.2020 | open | 250K |
Create time stamp stands indicates when the opportunity was created, and update time stamp indicate when the deal status has changed.
If I want to show open opportunities between 01.01.2020 and 31.12.2020 (i.e. during the year 2020), it would count the deal with ID 3 correctly, however, the open opportunities with ID 1 and ID 2 during that time (that got converted into deals in 2021) would not be counted using the deal status as a pivot filter. Hence, I decided to create a DAX functions to solve this issue.
I tried the following formula to get the total volume of all the open deals during a selected time period (in the example for 2020), but it does not work at all i.e. I get a pivot table without numbers i.e. blank fields:
Open Positions Date Range =
VAR StartDate = MIN(StartDateRangeTable[Start Date]) #this is from a date table called StartDateRange and will be used as # a Start Date Filter in the Pivot table
VAR EndDate = MAX(EndDateRangeTable[End Date]) #same logic as above(used as a End Date Filter)
RETURN
CALCULATE (
SUM (Query2[volume]),
FILTER(Query2,
Query2[create_timestamp] <= EndDate #nothing created after the End date selected in the pivot filter
&& Query2[update_timestamp] > EndDate #gives me open opportunities that were closed later but already labelled as deals
&& Query2[deal_status] = "Open" ) #gives me all deals that were open during that period
)
Thanks a lot in advance!!!
