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.

lbendlin's avatar
lbendlin
Super User
5 years ago

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

 

rclay78's avatar
rclay78
Frequent Visitor
5 years ago

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.