Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Clout
Helper III
Helper III

Moving minimum for the last 20+1 days

Hello guys,

 

Im struggling the whole day to find out how to create a calculated column by DAX for the moving minumum.

I want to determine for every date/row in my table the minimum of the last 20 days incl. the date looked at. Just like the Excel function MIN(A1:A21). 

 

I tried out these DAX functions:

 

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

-> this will give me blanks rows

 

MIN_Q = CALCULATE(
MIN('Flow'[QF_d + QR_d]),
FILTER (
ALL ('Flow'),
'Flow'[Datum] <= MAX('Flow'[Datum])
&& 'Flow'[Datum] >= MAX('Flow'[Datum]) - 20))
--> and this will give me the same one value in every row in my table which isnt't even the minimum.
 
Here is the sample file:
 
You can find the needed columns in the table "Flow"
 
Thank you in advance!
Can anyone help please? It seems like no one needed to determine the moving minimum in the history of PowerBI and so Im not able to find a solution in the internet.
 
Thank you in advance! 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @Clout 

 

For creating a new column, please try below.

 

MIN_Q =
VAR currentdate = Flow[Datum]
RETURN
CALCULATE (
MIN ( 'Flow'[QF_d + QR_d] ),
FILTER (
ALL ( 'Flow' ),
'Flow'[Datum] <= currentdate
&& 'Flow'[Datum] >= currentdate - 20
)
)

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
Jihwan_Kim
Super User
Super User

Hi, @Clout 

 

For creating a new column, please try below.

 

MIN_Q =
VAR currentdate = Flow[Datum]
RETURN
CALCULATE (
MIN ( 'Flow'[QF_d + QR_d] ),
FILTER (
ALL ( 'Flow' ),
'Flow'[Datum] <= currentdate
&& 'Flow'[Datum] >= currentdate - 20
)
)

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thats great, thank you very much @Jihwan_Kim !

 

I have another small question, is it possible to leave the first 21 rows of the calculated column [MIN_Q] blank and instead calculate the average of the whole [MIN_Q] column (expect the first 21 days) and put it in the first 21 days?

Hi, @Clout 

Thank you for your feedback.

I am not sure whether I understood your question correctly, but please check the below for creating a new column.

 

MIN_Q V2 =
VAR averageminq =
AVERAGE ( Flow[MIN_Q] )
VAR startingdate =
MINX ( Flow, Flow[Datum] )
RETURN
IF (
Flow[Datum] >= startingdate
&& Flow[Datum] <= startingdate + 20,
averageminq,
Flow[MIN_Q]
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hello @Jihwan_Kim , thank you for your suggestion, but I think I expressed myself badly.

 

I try to explain it with this logic:

 

IF Flow[Datum] = the first 20 rows in table THEN average([MIN_Q] ELSE [MIN_Q]

 

And in excel it would be looking like this.

 

MIN.PNG

 

I hope its now better understandable

Hi, @Clout 

Thank you for your feedback.

However, I think your explanation and the picture say a different thing.

I still cannot understand clearly.

I assume you want to have the below. Please check and let me know if it is what you are looking for.

Also, the sample pbix file's link is down below. You can check the FLOW table in it.

 

MIN_Q V2 =
VAR startingdate =
MINX ( Flow, Flow[Datum] )
VAR averageminqafter21 =
AVERAGEX (
FILTER ( ALL ( Flow ), Flow[Datum] > startingdate + 20 ),
Flow[MIN_Q]
)
RETURN
IF (
Flow[Datum] >= startingdate
&& Flow[Datum] <= startingdate + 20,
averageminqafter21,
Flow[MIN_Q]
)
 
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank you very much, you were a great help! 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors