How to determine 90 day total based on a column

[rclay78]

I am trying to calculate and identify a total for 90 day intervals based on SN and date. I would like to create a new column within this table. The "Total value 90 days" is the column I am attempting to create. Any suggestions would be very much appreciated. Thanks. 

SN	Date	Value	Total value 90 days
1234	1/1/2020	1.5
1234	1/30/2020	2.2	2.7
1234	7/5/2020	2.5	2.5
1234	11/1/2020	2.2	2.2
1122	1/2/2020	1
1122	2/14/2020	3.4
1122	3/1/2020	2.2	6.6
3345	4/2/2020	2.1
3345	5/1/2020	0.5	2.6
3345	11/23/2020	2.5	2.5
2345	1/6/2020	1
2345	2/28/2828	1.5	2.5
2345	6/30/2020	2.5
2345	8/4/2020	0.5	3

[v-angzheng-msft · Accepted solution]

 Hi, @rclay78 

According to your description, it will be a little difficult to complete this requirement.

I finally achieved the result you want by creating 2 auxiliary columns.

Measure1:

_Flag =
VAR _mindate =
    MINX ( FILTER ( ALL ( 'Table' ), [SN] = EARLIER ( [SN] ) ), [Date] )
VAR _datediff =
    DATEDIFF ( _mindate, [Date], DAY )
VAR _lastday =
    CALCULATE (
        MAX ( [Date] ),
        FILTER ( 'Table', [Index] = EARLIER ( [Index] ) - 1 && [SN] = EARLIER ( [SN] ) )
    )
VAR _lastdatediff =
    DATEDIFF ( _lastday, [Date], DAY )
RETURN
    SWITCH (
        TRUE (),
        [Date] = _mindate, 1,
        _datediff > 90, IF ( _lastdatediff > 90, 1, 0 ),
        0
    )

Measure2:

_Group =
CALCULATE (
    SUM ( 'Table'[_Flag] ),
    FILTER ( ALLSELECTED ( 'Table' ), [Index] <= EARLIER ( [Index] ) )
)

Measure3:

_Total value 90 days1 =
VAR _value =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Index] <= EARLIER ( [Index] )
                && [_Group] = EARLIER ( [_Group] )
        )
    )
VAR _maxindex =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER ( 'Table', [_Group] = EARLIER ( [_Group] ) )
    )
RETURN
    IF ( [Index] = _maxindex, _value, BLANK () )

Result:

Please refer to the attachment below for details

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

The provided data is a bit confusing.  I take it you want the 90 days previous to get the value (including the current value).

1st value should be 1.5

2nd value should be 3.7 and so on

So there are missing and wrong values (also a 2828 year in there).

Do you want to correct and confirm your desired output?

[rclay78]

yes, the second value should be 3.7. Sorry for the typos

[rclay78]

No, the first value should be blank. I am looking to return the total value of each SN within 90 days. I dont know why that 2828 is there. I doublechecked by spreadsheet, where I copied and pasted, and its 2020 there. Weird. Anyway, that number should be 2020.