Forum Discussion

masplin's avatar
masplin
Impactful Individual
6 years ago
Solved

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

  • masplin's avatar
    masplin
    6 years ago

    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

    • masplin's avatar
      masplin
      Impactful 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])
                                              )