Filtering a Table for a date range that exists in two date columns

[Dave_de_Castro]

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 = 

CALCULATE(Sum(a_NZ_JPR[J Profit NZD]),a_NZ_JPR[ME_PostDate] = a_NZ_JPR[ME_Rec_Date]). This is seems to work fine. 

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,

 

[Dave_de_Castro]

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?

[OwenAuger · Accepted solution]

Hi @Dave_de_Castro 

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?

[Dave_de_Castro]

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