Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Experts
how can you write a DAX measure to ignore the first 90 days based on the start date then the rolling average measure starts as shown
90 Rolling Average =
CALCULATE([FAME0/Palm 90 Day Rolling Correlation],
DATESBETWEEN(BioDiesels[Date],
MAX(BioDiesels[Date]) - 90,
MAX(BioDiesels[Date])))
Solved! Go to Solution.
Hi,
That measure is incorrect. Modify it as follows:
90 Rolling Average = if(min(Calendar[Date])-MINX(all(calendar),calendar[Date])<=90,blank(),CALCULATE([FAME0/Palm 90 Day Rolling Correlation],DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-90,MIN(Calendar[Date]))))
If this does not help, then share the download link of the PBI file and show the expected result.
Hi,
That measure is incorrect. Modify it as follows:
90 Rolling Average = if(min(Calendar[Date])-MINX(all(calendar),calendar[Date])<=90,blank(),CALCULATE([FAME0/Palm 90 Day Rolling Correlation],DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-90,MIN(Calendar[Date]))))
If this does not help, then share the download link of the PBI file and show the expected result.
Hi @Anonymous,
To create a DAX measure that ignores the first 90 days based on the start date and then calculates the rolling average, you can modify the existing measure by adding a condition to the CALCULATE function that filters out the first 90 days.
Here's an example of how you could modify the formula:
90 Day Rolling Average =
VAR StartDate = MIN(BioDiesels[Date])
RETURN
IF(
MAX(BioDiesels[Date]) - StartDate < 90,
BLANK(),
CALCULATE(
[FAME0/Palm 90 Day Rolling Correlation],
DATESBETWEEN(
BioDiesels[Date],
MAX(BioDiesels[Date]) - 90,
MAX(BioDiesels[Date])
)
)
)
The formula first defines a variable StartDate that represents the earliest date in the BioDiesels table. It then uses an IF statement to check if the difference between the current date and StartDate is less than 90 days. If it is, the formula returns BLANK(), which means the value will not be displayed. If the difference is greater than or equal to 90 days, the formula uses the original CALCULATE function to calculate the rolling average over the last 90 days.
Best regards,
Isaac Chavarria
If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.
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 |
---|---|
109 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |