Forum Discussion
Filtering a Table for a date range that exists in two date columns
Hi There,
I am fairly fresh here so please forgive me if this has been asked previously. I have a model which has a date table and a fact table with two date columns (ME_PostDate, ME_Rec_Date, note ME = month end). There is an active relationship between the ME_PostDate and Date_Table and an Inactive relationship between ME_Rec_Date and Date _Table.
I have calculated a measure to calculate the sum of a column (J Profit) where both ME_PostDate and ME_Rec_Date are the same. The measure used is JP_PDRD_Month =
The issue is that I need to calculate a measure where for a range of dates (i.e. the year to date) this works. Note applying a TOTALYTD or a DATESBETWEEN on JP_PDRD_Month seems to return the case where all month end dates are the same for each period and aggregating these, where what I am hoping to find a solution to is the sum of where a date range for both these columns is true = see below. Any help here would be greatly appreciated.
Link to workbook for the above workings, Date_Cols_Range_20241005
Greatly appreciated,
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?
3 Replies
- OwenAuger
Super User
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?
Author note: the second calulation in the above excel screen grab is the solution I am looking to apply as a measure in PowerBi. Many thanls
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?
