Forum Discussion

rclay78's avatar
rclay78
Frequent Visitor
5 years ago
Solved

Calculate value based on interval

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 ...
Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    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's avatar
Ashish_Mathur
Super User
5 years ago

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's avatar
rclay78
Frequent Visitor
5 years ago

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's avatar
    Ashish_Mathur
    Super User
    5 years ago

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