Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
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.
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.
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.
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.
Thank you very much, you were a great help! 🙂
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |