Forum Discussion
returning future date from date table based on inactive relationship and calculated filtering
Hi all
still working on this report and tried something new but hitting another wall. I am trying to have a sales stage table where each stage has a calculated velocity (in months). what i need is a date to be return based on the current month + the stage's velocity in a column. this way i can put matrices around target gaps and opportunites and such.
I have created a 'relative month to now' column in my custom date table. i have a sales stage table but it only has an inactive relationship to the date table ([today] column to [date] column) becuase i have some conflicting relationships that need to be active so i was going to USERELATIONSHIP in the calculation (below)
i think my problem is that there are more than 1 date to return when it filters the relative month to now and it is looking for one result. i tried a MIN to just return the first date of the month but still got an error.
how can i retriece the first day of the month that is the # of velocity months in the future from the date table? many thanks!
have found the solution here https://community.powerbi.com/t5/Desktop/Lookup-Min-Max-values-from-related-table/td-p/79481
i adjusted the column formula to below:
Impact Date =CALCULATE(MIN('Date'[Date]), FILTER('Date','Sales Stage Table'[Months to Impact] = 'Date'[Relative Month to Now]))
6 Replies
Anonymous
The information you have provided is not making the problem clear to me. Can you please explain with an example. Can you share sample data and sample output in a table format
Appreciate your Kudos.there are only 2 tables referenced, date and stage stage both are refrence tables. the output would be the stales stage table to look like below where 'start date' is the expected start date of all the opportunities in that sales stage based on the velocity months (how long it takes to win business).
does that help?
Stage Velocity Today Start Date 1 7 17/04 01/11/2020 2 6 17/04 01/10/2020 3 5 17/04 01/09/2020 4 4 17/04 01/08/2020 5 3 17/04 01/07/2020 Anonymous . Can share the sample for all the tables and specify the output. Seem like you need Open leads and when they coverts to opportunity. Some like this in HT blog, but seem like date are in different tables: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
have found the solution here https://community.powerbi.com/t5/Desktop/Lookup-Min-Max-values-from-related-table/td-p/79481
i adjusted the column formula to below:
Impact Date =CALCULATE(MIN('Date'[Date]), FILTER('Date','Sales Stage Table'[Months to Impact] = 'Date'[Relative Month to Now]))
