Averaging minimum value of each day

[Anonymous]

Hello,

I've just hit a wall on my dax comptencies :

I have a model with 1 data per hour, 24/7.

I'm need to create a measure which :

- Find the minimum value of the day

- If i'm at week granularity, I need the average of the minimum value of each day of the week

- If i'm at month granularity, I need the average of the minimum value of each day of the month

I don't think it is very complicated to do but I'm missing somethink with dax and the context filters.

Thanks for your help !

Jo

[mahoneypat]

You probably need something like this (may not be performant at large scale).

Avg Daily Min = AVERAGEX(VALUES('Date'[Date]), CALCULATE(MIN(Table[Value])))

Pat

[amitchandak]

@Anonymous , Assume value1 is a measure for which you need min at the day level. Till day level calculation can be

value1 = sum(Table[col1])

minx(values(Table[Date]), [Value1]) //Min at any level above day

[Anonymous]

Hi @amitchandak  thanks for your quick reply but your solution is only the easy part :

The difficult part is to get the right calculation at week and month level.

For example at week level :

Min(monday) = 4

Min(tuesday) = 4

Min(Wed) = 4

Min(thu) = 6

Min(Fri) = 8

Min(Sat) = 8

Min(Sun) = 8

The result I'm expecting at week level is the average of the min of each day. ie average (4+4+4+6+8+8+8) = 6.

Did I explain it better ? Thanks for your help !

Jo

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

First create a week and a month column to get the related week  and month number;

Then create a calculated column as below:

minvalue = IF('Table'[Value]=CALCULATE(MIN('Table'[Value]),ALLEXCEPT('Table','Table'[Date])),[Value],BLANK())

Then create a measure as below:

Measure = 
var _daysforweek=CALCULATE(DISTINCTCOUNT('Table'[Date]),FILTER(ALL('Table'),'Table'[Weeknum]=MAX('Table'[Weeknum])))
var _daysformonth=CALCULATE(DISTINCTCOUNT('Table'[Date]),FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])))
Return
IF(ISFILTERED('Table'[Weeknum]),DIVIDE(SUMX(FILTER(ALL('Table'),'Table'[Weeknum]=SELECTEDVALUE('Table'[Weeknum])),'Table'[minvalue]),_daysforweek),
IF(ISFILTERED('Table'[Month]),DIVIDE(SUMX(FILTER(ALL('Table'),'Table'[Month]=SELECTEDVALUE('Table'[Month])),'Table'[minvalue]),_daysformonth)))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!