Rolling Average to Ignore the first 90 then start

[Anonymous]

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])))

[Ashish_Mathur · Accepted solution]

Hi,

That measure is incorrect.  Modify it as follows:

1. Create a Calendar Table
2. Create a relationship (Many to One and Single) from the Date column of the Biodiesels table to the Date column of the Calendar Table
3. To your visual, drag Date from the Calendar Table
4. Modify your measure to

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.

[ichavarria]

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.