Need help using compound filters or nested filters from different tables

[I_Like_Power_BI]

I'm trying to code a measure that will tell me how much revenue has been "pulled in" from future quarters.  In other words, if a deal WAS going to close next quarter, but now it's going to close this quarter, then I want to sum it.

The code I have is below.

Basically, I need the sum of revenue where the close date is now this quarter (relative quarter = 0) from the newest snapshot of the data I have (MAX(Snapshot_Wk)) AND the close date USED TO BE in the future (relative quarter >0) from the oldest snapshot of the data (Min(Snapshot_Wk)).  I'm probably over thinking this - any help is appreciated!

Pulled_In = ( 
    CALCULATE(
        SUM(SFDC_OPP_HIST[UNWEIGHTED_REVN_SVCS]),
        FILTER('DIM_CALENDAR','DIM_CALENDAR'[FISC_QTR_RLTV]=0) && 
        FILTER('SFDC_OPP_HIST','SFDC_OPP_HIST'[SNAPSHOT_WK] = MAX(SFDC_OPP_HIST[SNAPSHOT_WK])),
        FILTER('DIM_CALENDAR','DIM_CALENDAR'[FISC_QTR_RLTV]>0) && 
        FILTER('SFDC_OPP_HIST','SFDC_OPP_HIST'[SNAPSHOT_WK] = MIN(SFDC_OPP_HIST[SNAPSHOT_WK]))
    )
) 
 

[I_Like_Power_BI · Accepted solution]

Thanks for the help, folks.  We couldn't figure it out, so we added it to the model directly.

[v-zhenbw-msft]

Hi @I_Like_Power_BI ,

Sorry for that we don't understand your issue clearly.

Could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

How is the SFDC OPP HIST related to DIM CALENDAR?

[I_Like_Power_BI]

Dim_Calendar[Date] = SFDC_OPP_HIST[OPP_CLSD_DT]

It's close date to master date. Does that help?

[AllisonKennedy]

How do you know which revenue matches which - is there a TransactionID that links or groups them together?
Your formula is doing some conflicting filters, and all the filters are using that Close Date to Master Date relationship, but there's nothing in your formula that tells Power BI that Snapshot_Wk 1, 2 and 10 all relate to the same transaction or object. We need to find that information to answer your question.

[I_Like_Power_BI]

The Deal ID is the unique identifier in the SFDC_OPP_HIST table.  Each Deal ID has a rolling 13 snapshots (1 snapshot per week for the last 13 weeks). Each of those weeks have close dates and revenue associated with them.

I'm attempting to use the Minimum snapshot (it's a number value: MIN(SFDC_OPP_HIST[SNAPSHOT_WK])) as the original value and the Maximum snapshot (MAX(SFDC_OPP_HIST[SNAPSHOT_WK])) as the new value.  That would be fine in a vacuum, but I also need to know if the close date was originally set to some future quarter, which is where the Master Calendar comes in... the SFDC_OPP_HIST table only has dates, whereas the Master Calendar has relative quarter (0 = current quarter, >0= future quarters).

[AllisonKennedy]

Provided that you use the measure in a visual with Deal ID, you could try an IF statement: 

Pulled_In =

VAR _MinSnap = MIN(SFDC_OPP_HIST[SNAPSHOT_WK])

VAR _MaxSnap = MAX(SFDC_OPP_HIST[SNAPSHOT_WK])

VAR _MinQtr = MINX(FILTER(SFDC_OPP_HIST, SFDC_OPP_HIST[SNAPSHOT_WK]=_MinSnap) ,RELATED(Dim_Calendar[FISC_QTR_RLTV]))

VAR _MaxQtr = MAXX(FILTER(SFDC_OPP_HIST, SFDC_OPP_HIST[SNAPSHOT_WK]=_MaxSnap), RELATED(Dim_Calendar[FISC_QTR_RLTV]))

RETURN IF(_MinQtr<>0&&_MaxQtr=0, SUMX(FILTER(SFDC_OPP_HIST, SFDC_OPP_HIST[SNAPSHOT_WK]=_MaxSnap), [UNWEIGHTED_REVN_SVCS]))