Calculate value based on interval

[rclay78]

I am trying to figure out how to calculate a 90 day total grouped by date and SN. I want to create a calculated column [Total Value 90 Days] that adds the [Value] over 90 days based on [SN]. I would like the column to look like th eone below. Any help would be greaty apprecated 

SN             Date       Value   Total value 90 days

1234	1/1/2020	1.5
1234	1/30/2020	2.2	3.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/2020	1.5	2.5
2345	6/30/2020	2.5
2345	8/4/2020	0.5	3

[v-henryk-mstf · Accepted solution]

Hi @rclay78 ,

According to your request, I did the following test: whether the created col statistic is a duplicate value. Reference is as follows:

Col_ =
CALCULATE (
    COUNTROWS ( Data ),
    FILTER (
        ALL ( Data ),
        Data[Value] = EARLIER ( Data[Value] )
            && Data[SN] = EARLIER ( Data[SN] )
    )
)

11 = 
IF (
    CALCULATE (
        COUNTROWS ( Data ),
        FILTER (
            Data,
            Data[SN] = EARLIER ( Data[SN] )
                && Data[Date] <= EARLIER ( Data[Date] )
        )
    ) = 1,
    BLANK (),
    IF(
    CALCULATE (
        SUM ( Data[Value] ),
        FILTER (
            Data,
            Data[SN] = EARLIER ( Data[SN] )
                && Data[Date]
                    < EARLIER ( Data[Date] ) - 90
                && Data[Date] <= EARLIER ( Data[Date] )
        )
    ) && Data[Col_]=1 ,BLANK(),    CALCULATE (
        SUM ( Data[Value] ),
        FILTER (
            Data,
            Data[SN] = EARLIER ( Data[SN] )
                && Data[Date]
                    >= EARLIER ( Data[Date] ) - 90
                && Data[Date] <= EARLIER ( Data[Date] )
        )
    )
))

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

This should ideally be written as a measure directly in the visual but since you have asked for a calculated column, try this calculated column formula

=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[SN]=EARLIER(Data[SN])&&Data[Date]<=EARLIER(Data[Date])))=1,BLANK(),CALCULATE(SUM(Data[Value]),FILTER(Data,Data[SN]=EARLIER(Data[SN])&&Data[Date]>=EARLIER(Data[Date])-90&&Data[Date]<=EARLIER(Data[Date]))))

Hope this helps.

[rclay78]

I tried this and it is only returning values where there was more than 1 instance of [Value] in 90 days. Instances where [SN] only has 1 occurance of [Value] are all blank. 

[Ashish_Mathur]

Hi,

See if this modified version works

=if(AND(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[SN]=EARLIER(Data[SN])&&Data[Date]<=EARLIER(Data[Date])))=1,CALCULATE(COUNTROWS(Data),FILTER(Data,Data[SN]=EARLIER(Data[SN]))>1)),BLANK(),CALCULATE(SUM(Data[Value]),FILTER(Data,Data[SN]=EARLIER(Data[SN])&&Data[Date]>=EARLIER(Data[Date])-90&&Data[Date]<=EARLIER(Data[Date]))))

[lbendlin]

what's the rule for the blanks?  Do you need to have more than one value before you calculate your 90 day total?

[rclay78]

There should only be a blank value if there is more than one [Value] for the same [SN] in 90 days. For instances where there is only 1 occurance of a [Value] in 90 days then only that value should be in the column. Make sense? Thanks.