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.

v-henryk-mstf's avatar
v-henryk-mstf
Community Support
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.