DAX Measure error to ignore first 90 days

[Anonymous]

Hi Experts

How can i correct the following measure as it has an error

Rolling Sales90D = CALCULATE( [Total Sales], DATESBETWEEN( Dates[Date], DATEADD(MIN(Dates[Date]), 90, DAY), MAX(Dates[Date]) ) )

This modified formula uses the DATEADD function to add 90 days to the minimum date in the Dates[Date] column, which represents the start date for the rolling total. The DATESBETWEEN function is then used to filter the Dates[Date] column to include only dates between the start date and the maximum date in the column. Finally, the CALCULATE function applies the Total Sales measure to this filtered range of dates, giving you the rolling total of sales for the 90-day period starting after the earliest date in the column.

[visheshjain]

Hi @Anonymous,

Please can you try the following code and see if it works for you.

__Roll90D =
VAR MinCalDate =
    CALCULATE (
        MIN ( sales[invdt] ),
        FILTER ( ALL ( A_Calendar[Date] ), [Total Sales Amount] > 0 )
    ) + 90
VAR CurrDate =
    MAX ( A_Calendar[Date] )
VAR CurrDate90 = CurrDate - 90
VAR Xsale =
    CALCULATE (
        [Total Sales Amount],
        FILTER (
            ALL ( A_Calendar[Date] ),
            A_Calendar[Date] <= CurrDate
                && A_Calendar[Date] >= CurrDate90
        )
    )
RETURN
    IF ( CurrDate >= MinCalDate, Xsale )

Hope this helps.

Thank you,

Vishesh Jain

[Anonymous]

measures are in the sample data 90 Days Rolling Average 2012-2023

thanks

[visheshjain · Accepted solution]

Hi @Anonymous,

I am unable fine the index column in the Biosales Table.

The measure I suggested is ignoring the first 90 days and then starting the calculation of the last 90 days.

Here is a revised version based on your data model; all you have to do, is replace the Total Sales Amount with your desired measure.

Then the output of this can be divided by 90 to get the running average.

__Roll90D = 
VAR MinCalDate =
    CALCULATE (
        MIN ( BioSales[Date] ),
        FILTER ( ALL ( DimDate[Date] ), [Total Sales Amount] > 0 )
    ) + 90
VAR CurrDate =
    MAX ( DimDate[Date] )
VAR CurrDate90 = CurrDate - 90
VAR Xsale =
    CALCULATE (
        [Total Sales Amount],
        FILTER (
            ALL ( DimDate[Date] ),
            DimDate[Date] <= CurrDate
                && DimDate[Date] >= CurrDate90
        )
    )
RETURN
    IF ( CurrDate >= MinCalDate, Xsale )

Hope this helps.

Thank you,

Vishesh Jain

[Anonymous]

Thanks - it nearly works small error...posting new question

[Anonymous]

https://www.dropbox.com/s/009d9n8v2bwi1oz/Testsample.pbix?dl=0 

Hi Vishesh Jain - i have an index column in my FACT Table Biosales - why can we not ignore sales for all index numbers less then 90 and from 90 onwards apply the Rolling Sales90D sales calculation. 

PS thanks for the feed back but the above is not working - see attached sample file