Dax Creating baseline value measure

[Anonymous]

Hi there everyone. I have a problem I have been trying to solve. I am a newbie so please bear with me

The problem involves writing baseline values as a Dax measure,

• I have to create a“ baseline value” measure for hours worked for each location.

• Such that: the baseline value is based on the average of all "actual hours worked" per location. (This Value must remain constant regardless of the date,

• Option b for a baseline:

each week baseline should be based on the average hours of 3 weeks prior, such that it dynamically changes as time goes on.

I have the following sample table of work hours below:

Just a similar example of what I am trying to achieve in a visual form

 

I would appreciate any help I can get on this. Thank you all. 

[rfigtree]

assume you mean there is another table with the static values.

easiest thing would be to  join the two tables together.

[rfigtree]

you might have to tweak depending on how your data model is set up but this might give you a starting point.

mbaseline:=
var _MaxDate=MIN(Table1[date])
var _MinDate= _MaxDate-21
return 
     CALCULATE(AVERAGE(Table1[actual]),
               FILTER(all(Table1[date]),
                      Table1[date]>_MinDate && Table1[date]<=_MaxDate
                     )
               )

[Anonymous]

Hi there, Thanks for your input, and will try this out. Also, I would like to know how I can make an overall static baseline value for each location so that it remains unaffected by the date value changes.