Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dax Creating baseline value measure

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. 

 

 

 

 

3 Replies

  • rfigtree's avatar
    rfigtree
    Resolver III
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • rfigtree's avatar
    rfigtree
    Resolver III
    5 years ago

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

    easiest thing would be to  join the two tables together.