I have a table with a number of columns and two distinct date Columns. “Created Date” and "Closed Date"
Close Date is the default relationship to my date dimensions table as this is what a majority of the reporting is based on.
I then needed to create a measure that uses “Date Created” so I can report on all new entries added to the table.
Total Funnel = CALCULATE(sum(Pipeline[Total Amount in USD]),USERELATIONSHIP(Pipeline[Created Date],Dates[Date]),Pipeline[Forecast Category]<>"Omitted")
This returns the correct value for all table entries created in the filter year. e.g. FY19
However, when I use the following DAX express to group the values, I get a different amount.
Funnel Grouping = CALCULATE( [Total Funnel], Filter(VALUES( Pipeline[Opportunity ID] ), COUNTROWS( FILTER('Opportunity Grouping', [Total Funnel] >= 'Opportunity Grouping'[Min] && [Total Funnel] < 'Opportunity Grouping'[Max] )) > 0) )
e.g. Total Funnel = 1000 ; Funnel Grouping = 900.
Investigating the discrepancy between the values I notice that the missing entries excluded from the Funnel Grouping Total, these have a Date Closed outside of the current filter Year (FY19) which is why I suspect the Funnel Grouping Dax measure is not including these.
Please can you advise, how I should modify the Funnel Grouping measure to take account of this so I can include ALL records where the Date Created is in the current Filter Year FY19 no matter what the Date Close is.