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 like the column to look like th eone below. Any help would be greaty apprecated 

 

 

 

SN             Date       Value   Total value 90 days

12341/1/20201.5 
12341/30/20202.23.7
12347/5/20202.52.5
123411/1/20202.22.2
11221/2/20201 
11222/14/20203.4 
11223/1/20202.26.6
33454/2/20202.1 
33455/1/20200.52.6
334511/23/20202.52.5
23451/6/20201 
23452/28/20201.52.5
23456/30/20202.5 
23458/4/20200.53
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.

6 Replies

  • 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. 

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