Forum Discussion
Helper IINeed help using compound filters or nested filters from different tables
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]))
)
)
Thanks for the help, folks. We couldn't figure it out, so we added it to the model directly.
7 Replies
- AllisonKennedy
Community Champion
How is the SFDC OPP HIST related to DIM CALENDAR?
I_Like_Power_BIDim_Calendar[Date] = SFDC_OPP_HIST[OPP_CLSD_DT]
It's close date to master date. Does that help?
- AllisonKennedyHow 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.
v-zhenbw-msftCommunity Support
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.
- I_Like_Power_BI
Thanks for the help, folks. We couldn't figure it out, so we added it to the model directly.
