Forum Replies Created

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts

  • pjmorris
    Participant
    • Started: 2
    • Replies: 5
    • Total: 7

    Apologies – I misunderstood what was happening!

    After further investigation I can see that the tblResource is being filtered by the date.  It is not being filtered by [Booking Group].  Ive now tried using the following:

    =sum(tblTasksSimple[Effort])*calculate([ActualAvailableResource],tblResource[Booking Group]) without success.

    and [ActualAvailableResource]=sumx(tblResource,tblResource[Available Hrs]*RELATED(tblCalendar[Daily % of Annual]))

    Hope this helps.

    Regards

    Peter

    in reply to: Filtered Data on Two Different Date Columns #11108

    pjmorris
    Participant
    • Started: 2
    • Replies: 5
    • Total: 7

    Hi Bob,

    I’ve now had a chance to implement your suggestions on my live data – and I’m getting there.  However, (theres always a however) I don’t seem to be able to make my cumulative measure work.  The measure for an individual months ‘Rectified’ data is:

    [Calc Rectified]:=COUNTROWS(  FILTER(  CALCULATETABLE(‘OPDEF Signals Data’, USERELATIONSHIP(Calendar[Date],’OPDEF Signals Data'[Rectified])),’OPDEF Signals Data'[Raised]>=[StartDate] && ‘OPDEF Signals Data'[Raised]<=[EndDate]))

    which is based on your models, but no matter what I do I don’t seem to be able to remove the column filter for start date in order to get a cumulative total.

    Grateful if you could provide more advice!

    Many thanks.

    Peter

    in reply to: Filtered Data on Two Different Date Columns #11075

    pjmorris
    Participant
    • Started: 2
    • Replies: 5
    • Total: 7

    Hi Tom,

    2013 at work (where I need the answer) and 2016 at home (from which I produced the example data set attached above).

    Regards

    Peter

    in reply to: Filtered Data on Two Different Date Columns #11071

    pjmorris
    Participant
    • Started: 2
    • Replies: 5
    • Total: 7

    Hi Tom,

    Many thanks for the reply, and the use of Filter obviously worked.  However, I now find that the [Rectified] measure returns nothing.  So I wonder whether the structure of the [Start Date] measure (definition below) is in conflict with the Filter statement?  I have a Timeline that sets the period over which I want to view the data and [Start Date] needs to return the first date selected.  I added the:

    ALL(‘Calendar'[Year],’Calendar'[Short Month])

    to remove the filtering caused by having those two columns on the Pivot otherwise the [Start Date] merely returns the start of each column.  I wonder whether there is some conflict because of the USERELATIONSHIP.  The output I need for [Rectified] should be a count of those OPDEFs raised since the start of the Timeline that were Rectified in the current month.  For info the definition of [Start Date] is:

    [Start Date]:=CALCULATE(MIN(‘Calendar'[Dates]), ALL(‘Calendar'[Year],’Calendar'[Short Month]),
    ALLSELECTED(‘Calendar'[Dates]))

    I’d be really grateful for any further help or advice.

    Regards

    Peter

    PS. This was sent from my work forum membership and the original post from the one I setup from home.

    in reply to: Incorrect GrandTotals #10915

    pjmorris
    Participant
    • Started: 2
    • Replies: 5
    • Total: 7

    Hi Tom,

    Firstly, your assumptions are all spot-on and apologies for the error in SumofEffort.

    Secondly, your answer works perfectly for which I’m really grateful – I have been struggling with this for weeks and have been learning Powerpivot at the same time! Its a really useful tool, but quite a different way of thinking from my previous approach without the ‘Power’ tools.

    Again, many thanks – the help is very much appreciated.

    Regards

    Peter

Viewing 5 posts - 1 through 5 (of 5 total)