Forum Discussion
Filtering a Table for a date range that exists in two date columns
I would suggest a measure like this:
P_PDRD_Month = VAR ME_Rec_Date_Filter = CALCULATETABLE ( VALUES ( a_NZ_JPR[ME_Rec_Date] ), USERELATIONSHIP ( a_NZ_JPR[ME_Rec_Date], Date_Table[Date] ) ) RETURN CALCULATE ( SUM ( a_NZ_JPR[J Profit NZD] ), -- Apply ME_Rec_Date_Filter simultaneously with filter based on active relationship. KEEPFILTERS ( ME_Rec_Date_Filter ) )The idea is to create a filter on ME_Rec_Date using the inactive relationship, then combine this with the Date_Table filter combined with the active relationship.
(I couldn't open the workbook as it seems to be a local link.)
Does this work for you?
Hi Owen,
Apologies for the delay here - this has worked and further it works in the context of applying a date range (YTD or datesbetween). That is J Profit NZD is calculated correctly for the time between a range of dates for both PostDate and Rec_Date (as opposed to summing each instance where PostDate = Rec_Date over a range of dates).
Thank you sincerely for your headspace here!
Just to understand what this function is doing.
1. The variable applies a filter context to the table a_NZ_JPR using the inactive relationship a_NZ_JPR[ME_Rec_Date] to Date_Table[Date].
2. Sum the J Profit NZD based on the active relationship a_NZ_JPR[ME_PostDate] to Date_Table[Date] while keeping the filters already applied in the variable ME_Rec_Date_Filter
one query please - is the reason for the use of the VALUE(a_NZ_JPR[ME_Rec_Date]) so that it returns a scalar value?