Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I want to calculate the minimum value of rolling 12 month return for the following data set
I use the following code to calculate rolling 12 return and it can calculate the result correctly.
Rolling12M = CALCULATE( SUM(DataBase[Return]), DATESINPERIOD( DataBase[Date], LASTDATE(DataBase[Date]), -12, MONTH ) )
However, i use MINX to calculate min value of rolling 12m return, but it return the min value of single month return
my code is as follows:
MaxDD = MinX(Database,[Rolling12M])
For example, I use [Rolling12M] code to calculate EUR Rolling12M return and the graph show minimum value is -24.39%
But when i use [MaxDD] code to calculate min value of [Rolling12M], it shows -7.39% (worst month return)
Does anyone can help me?
Thanks!
Solved! Go to Solution.
I use the following code to solve the problem temporary. However, the -120 is manual. Can i dynamic get the count between lastmonth and firstmonth?
MaxDD = MINX(DATESINPERIOD(DataBase[Date],LASTDATE(DataBase[Date]), -120, MONTH),[Rolling12M])
I use the following code to solve the issue.
MonthCount = CALCULATE (COUNT ( Database[Date] ),FILTER (ALLSELECTED ( Database ),Database[Date] <= MAX ( Database[Date] )))
I use the following code to solve the problem temporary. However, the -120 is manual. Can i dynamic get the count between lastmonth and firstmonth?
MaxDD = MINX(DATESINPERIOD(DataBase[Date],LASTDATE(DataBase[Date]), -120, MONTH),[Rolling12M])
Hi,
I'm looking for exactly the same but than the minimum on month base and not on day base.
Any idea?
Kr,
Valérie
I use the following code to solve the issue.
MonthCount = CALCULATE (COUNT ( Database[Date] ),FILTER (ALLSELECTED ( Database ),Database[Date] <= MAX ( Database[Date] )))
Hi @andychu221,
It's pleasant that your problem has been solved, could you please mark your reply as Answered?
Regards,
Daniel He
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |