Forum Discussion
Problems getting numbers displayed due to relationship problems (and that's not my wife!)
This is is pretty hard to expalin so created a small version of the file at this location Model
Sales Pipeline contains a list of deals
QB time contains timesheets for time spent on each deal
Active relationship betwen QB Time [Date] and datetable
I've got as far as this with dates coming from Datetable
Where (Earliest date is Min of close date or first entry in QB Time)
Measure = CALCULATE( sum('Sales Pipeline'[Est Hours]),
FILTER(
'Sales Pipeline',
'Sales Pipeline'[Earliest Date]<=MAX(DateTable[Day])
)
)Measure2 = CALCULATE(sum('Sales Pipeline'[Est Hours]),ALL(DateTable))
I have 2 issues I can't work out
1. 19-VR-845 has no time as yet recorded in QB time i.e. project has not started yet. I want to show the est hours from the Sales Pipeline[close date] of 5/9/19 forward. There is no relation between this deal and the datetable because there is no data in the linking table of QB Time. As soon as I try to filter Measure2 with anythnig to do with date it just disappears. I tried creating an inactive relationship between Sales Pipeline[Earliest date] and Datetable then add a USERELATIONSHIP like this but also just disappeared
Measure3 = CALCULATE([Measure2],
FILTER(
'Sales Pipeline',
'Sales Pipeline'[Earliest Date]<=MAX(DateTable[Day])
),
USERELATIONSHIP('Sales Pipeline'[Earliest Date],DateTable[Day])
)
So it should look like the other 3 [Measure] column with just 68.18 from Sep19 onwards, the Earliest date
2. Secondly I need to project these number sin to the future, but Measure just stops at Dec19 as that is the last entry in the QB Time data. Again any filter I tried to apply to [Measure2] resulted in same as [Measure] for the first 3 which all have data in QB Time. The result I'm looking for is the same as [Measure], starting at Earliest date, but continuing for months beyond Dec19
I've tried about 30 different ideas and all failed so a bit out of ideas. Any suggestions welcome to resolve either or both issues (possibly they are related)
Mike
I got it working having suddenly hada brain wave. I had to convert my column value Sales Pipeline[Earliest date] intoa measure [Project start Date] so I could use it in a filter on the Datetable.
Project Est Hours = CALCULATE(SUM('Sales Pipeline'[Est Hours]), FILTER( ALL(DateTable), DateTable[Day]>=[Project start Date] && DateTable[Day]<=MAX(DateTable[Day]) ), USERELATIONSHIP('Sales Pipeline'[Earliest Date],DateTable[Day]) )
2 Replies
- V-lianl-msftCommunity SupportHi masplin ,I'm also researching in your needs. See if this thread can help you:Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- masplinImpactful Individual
I got it working having suddenly hada brain wave. I had to convert my column value Sales Pipeline[Earliest date] intoa measure [Project start Date] so I could use it in a filter on the Datetable.
Project Est Hours = CALCULATE(SUM('Sales Pipeline'[Est Hours]), FILTER( ALL(DateTable), DateTable[Day]>=[Project start Date] && DateTable[Day]<=MAX(DateTable[Day]) ), USERELATIONSHIP('Sales Pipeline'[Earliest Date],DateTable[Day]) )