Determine the minimum of the last 20 days + today but start only at the 21th day of the year

[Clout]

Hello guys,

I have a calculated table with values from 2015 till 2021 which will grow over the years.

Now I want to determine for every single date the minimum of the last 20 days incl. today. But the determination of the minimum should only start at the 21th of the year till the last day of the year.

The first blank values of the first 20 days should be calculated by the average of the determinated minimum values over the whole year. 

As you can see, the values [QF,d] of the 01.01 till 20.01 are the average of the whole year (21.01 - 31.12). And the values [QF,d] at the beginning of 21.01 are the minimas of [QF,d + QR,d] within the last 20 days + today.

And its very important to do this procedure seperate with every year.

My first try looks like this:

MIN_Q = CALCULATE(MINX('Abflüsse', 'Abflüsse'[QF_d + QR_d]), DATESINPERIOD('Abflüsse'[Datum], TODAY(), -20, DAY))

But I get only blank fields in my calculated columns.

Thank you in advance!

[v-robertq-msft · Accepted solution]

Hi, @Clout 

According to your description and sample pictures, I can roughly understand your logic, and I created two calculated columns to achiev this, you can try my method:

I created two calculated columns like this:

Min =

MINX(FILTER(ALL('Flow'),[Datum]<=EARLIER([Datum])&&[Datum]>=EARLIER([Datum])-20),[QF_d + QR_d])

minimum of the last 20 days + today =

var _average=
AVERAGEX(FILTER(ALL('Flow'),[Datum]>=DATE(YEAR(EARLIER([Datum])),1,21)&&[Datum]<=DATE(YEAR(EARLIER([Datum])),12,31)),[Min])

return

IF(MONTH([Datum])=1&&DAY([Datum])<=20,_average,[Min])

Then I create a table chart to display the data:

And you can get the correct data as you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Clout , Create two new columns like

Diff = mod(datediff([datum], today(), day)+1,20) //remove +1 if needed

Avg = Minx(filter(Table, [Diff] = earlier([Diff])),[QF,d + QR,d] )

[Clout]

Hello @amitchandak, thank you for your suggestion but this solution doesn't work. I get 0 values, even if there are no 0 values within these 21 days. 

I attached a sample file, the columns you can find in the table "Flow".

Sample File 

EDIT: Just the moving minumum of the last 21 days would be enough, without excluding the first 21 days