topic Re: Dax Creating baseline value measure in DAX Commands and Tips

Dax Creating baseline value measure

Anonymous — Thu, 04 Mar 2021 02:57:30 GMT

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.

Re: Dax Creating baseline value measure

rfigtree — Thu, 04 Mar 2021 06:05:12 GMT

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 ) )

Re: Dax Creating baseline value measure

Anonymous — Thu, 04 Mar 2021 16:57:24 GMT

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.

Re: Dax Creating baseline value measure

rfigtree — Fri, 05 Mar 2021 07:04:16 GMT

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

easiest thing would be to join the two tables together.