Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
1234 | 1/1/2020 | 1.5 | |
1234 | 1/30/2020 | 2.2 | 3.7 |
1234 | 7/5/2020 | 2.5 | 2.5 |
1234 | 11/1/2020 | 2.2 | 2.2 |
1122 | 1/2/2020 | 1 | |
1122 | 2/14/2020 | 3.4 | |
1122 | 3/1/2020 | 2.2 | 6.6 |
3345 | 4/2/2020 | 2.1 | |
3345 | 5/1/2020 | 0.5 | 2.6 |
3345 | 11/23/2020 | 2.5 | 2.5 |
2345 | 1/6/2020 | 1 | |
2345 | 2/28/2020 | 1.5 | 2.5 |
2345 | 6/30/2020 | 2.5 | |
2345 | 8/4/2020 | 0.5 | 3 |
Solved! Go to Solution.
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.
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.
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.
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]))))
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |