Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rclay78
Frequent Visitor

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
1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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

v-henryk-mstf_0-1618911916867.png

 


Best Regards,
Henry

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

View solution in original post

6 REPLIES 6
v-henryk-mstf
Community Support
Community Support

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

v-henryk-mstf_0-1618911916867.png

 


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
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

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

 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.