DAX Measure to ignore first 90 days

[Anonymous]

Hi Experts

The following measure in the sample date (see below) works up to 03 April 2020 then goes out of sink.... all the data point before that data are 100% correct (measure _Roll90d) 

I can see data point for first 90 days there should be non....column _Roll90D then the measure should work..

https://www.dropbox.com/s/9ads9dk6kniwxwb/Testsample%20%281%29.pbix?dl=0 

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

[Anonymous]

New Measure which ignores the first 90 days but the expected result is wrong on the corrleation 

__Roll90D2 = 
VAR MinCalDate =
    CALCULATE (
        MIN ( BioSales[Date] ),
        ALL ( BioSales[Date])
    ) + 90
VAR CurrDate =
    MAX ( BioSales[Date] )
VAR CurrDate90 = CurrDate - 90
VAR Xsale =
    CALCULATE (
        [FAME0/Palm 90 Day Rolling Correlation],
        FILTER (
            ALL ( BioSales[Date]),
            BioSales[Date]<= CurrDate
                && BioSales[Date]>= CurrDate90
        )
    )
RETURN
    IF ( CurrDate >= MinCalDate, Xsale )

[v-shex-msft]

Hi @Anonymous,

The shared file seem not able to be viewed, can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Community. Thanks for looking at the question. Here is the really issue

See sample file

https://www.dropbox.com/s/wewh7bgfu1x039d/Sample_Datapbix.pbix?dl=0 

Expect Result in Excelhttps://www.dropbox.com/scl/fi/tw8jzia4624kzkk61usja/Test_Data.xlsx?dl=0&rlkey=zwt0opv6drj8z80i4ew4p... 

Kindly refer to the last 2 column in the table. I am trying to work out the 90 day rolling based on The Correlation Coefficient measure should start on the 10 may 2020 as that is data point 90 based on the Index Value.

The expected end results are shown in the Excel with the working calculation in Excel - i cannot work out why the results go wrong after the 10 May 2012.

[v-shex-msft · Accepted solution]

HI @Anonymous ,

I check the sample file and find it seem nested lots of calculation in the invoked measure formula. 

For these calculations, they will get different result when you calculated on the detail and total levels.

Please check the following blog to add a variable with summarize function in your formal to help the invoked formula to calculate on the detail levels, then you can use iterator function sumx to summary these results.

Measure Totals, The Final Word 
Regards,

Xiaoxin Sheng