measure with different offsets on date, depending on data coming from 2 facts table

[arq52]

Hello,
I have a task table, with due dates and quantities:

A table of resources requested per task, different steps with the unit load and a number of days in advance of the due date:

Current connections:

I'm trying to calculate the workload by resource, per weeks generated by these tasks, thanks to the offset from the due date.

   * total load = UN_load * quantity
   * each of these loads positioned at ending date + offset
Expected "results" :

I would know how to do this with powerquery, but with my actual datas, it would make me generate a results file of over 2 million records. I guess that it may be feasable with dax with 10 to 20 times less recordings.
Thank you in advance for your help.

[arq52]

Hello Daniel,

it's perfect!

Now just to check the performance point of vue with my real datas.

Thanks again.

[wdx223_Daniel]

@arq52  i got the result you expected

Load_Date:=IF(COUNTROWS('resource_per_task')*COUNTROWS('schedule'),MAX('schedule'[ending date])+MAX('resource_per_task'[offset]))
****************************************
Total_Load:=IF(COUNTROWS('resource_per_task')*COUNTROWS('schedule'),MAX('schedule'[quantity])*MAX('resource_per_task'[UN_load]))

[arq52]

Thank you Daniel,

Not far from what I'm expecting, but I want to be able to do pivot table and link the load to the calendar date. With this measure, the load is linked to the ending date and not to the "load date":

[wdx223_Daniel · Accepted solution]

@arq52  is this what you want?

Total Load :=
SUMX (
    CROSSJOIN (
        VALUES ( task[tasks] ),
        VALUES ( resource_per_task[resource] ),
        VALUES ( 'Calendar'[Date] )
    ),
    VAR _CurrentDate = 'Calendar'[Date]
    VAR _EndingDate =
        CALCULATE ( MIN ( schedule[ending date] ), 'Calendar'[Date] >= _CurrentDate )
    VAR _Offset =
        CALCULATE ( SUM ( resource_per_task[offset] ) )
    VAR _Load =
        CALCULATE ( SUM ( schedule[quantity] ), 'Calendar'[Date] = _EndingDate )
            * CALCULATE ( SUM ( resource_per_task[UN_load] ) )
    RETURN
        IF ( _CurrentDate = _EndingDate + _Offset, _Load )
)

[arq52]

Hi,

I have tryed

total load :=
SUMX (
  'schedule';
  'schedule'[quantity]
    * CALCULATE (
      VALUES ( 'ressource_per_tasks'[UN_load] );
      FILTER (
        'ressource_per_tasks';
        'ressource_per_tasks'[tasks] = 'schedule'[tasks]
      );
      FILTER (
        'Calendar';
        'Calendar'[date] = 'schedule'[ending date] + MAX('ressource_per_tasks'[offset])
    )
  )
)

With "MAX", I have "remove" an error about "not unique value for offset column".

But when I try to use the mesure in a pivot table, I get a "calculation error with the measure : a table with several values have been provided, but a unique value was expected".

Do you have an idee to solve that ?

Best regards