Forum Discussion
How to determine 90 day total based on a column
I am trying to calculate and identify a total for 90 day intervals based on SN and date. I would like to create a new column within this table. The "Total value 90 days" is the column I am attempting to create. Any suggestions would be very much appreciated. Thanks.
| SN | Date | Value | Total value 90 days |
| 1234 | 1/1/2020 | 1.5 | |
| 1234 | 1/30/2020 | 2.2 | 2.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/2828 | 1.5 | 2.5 |
| 2345 | 6/30/2020 | 2.5 | |
| 2345 | 8/4/2020 | 0.5 | 3 |
Hi, rclay78
According to your description, it will be a little difficult to complete this requirement.
I finally achieved the result you want by creating 2 auxiliary columns.
Measure1:
_Flag = VAR _mindate = MINX ( FILTER ( ALL ( 'Table' ), [SN] = EARLIER ( [SN] ) ), [Date] ) VAR _datediff = DATEDIFF ( _mindate, [Date], DAY ) VAR _lastday = CALCULATE ( MAX ( [Date] ), FILTER ( 'Table', [Index] = EARLIER ( [Index] ) - 1 && [SN] = EARLIER ( [SN] ) ) ) VAR _lastdatediff = DATEDIFF ( _lastday, [Date], DAY ) RETURN SWITCH ( TRUE (), [Date] = _mindate, 1, _datediff > 90, IF ( _lastdatediff > 90, 1, 0 ), 0 )Measure2:
_Group = CALCULATE ( SUM ( 'Table'[_Flag] ), FILTER ( ALLSELECTED ( 'Table' ), [Index] <= EARLIER ( [Index] ) ) )Measure3:
_Total value 90 days1 = VAR _value = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), [Index] <= EARLIER ( [Index] ) && [_Group] = EARLIER ( [_Group] ) ) ) VAR _maxindex = CALCULATE ( MAX ( 'Table'[Index] ), FILTER ( 'Table', [_Group] = EARLIER ( [_Group] ) ) ) RETURN IF ( [Index] = _maxindex, _value, BLANK () )Result:
Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- HotChilli
Community Champion
The provided data is a bit confusing. I take it you want the 90 days previous to get the value (including the current value).
1st value should be 1.5
2nd value should be 3.7 and so on
So there are missing and wrong values (also a 2828 year in there).
Do you want to correct and confirm your desired output?
No, the first value should be blank. I am looking to return the total value of each SN within 90 days. I dont know why that 2828 is there. I doublechecked by spreadsheet, where I copied and pasted, and its 2020 there. Weird. Anyway, that number should be 2020.
yes, the second value should be 3.7. Sorry for the typos
- v-angzheng-msft
Community Support
Hi, rclay78
According to your description, it will be a little difficult to complete this requirement.
I finally achieved the result you want by creating 2 auxiliary columns.
Measure1:
_Flag = VAR _mindate = MINX ( FILTER ( ALL ( 'Table' ), [SN] = EARLIER ( [SN] ) ), [Date] ) VAR _datediff = DATEDIFF ( _mindate, [Date], DAY ) VAR _lastday = CALCULATE ( MAX ( [Date] ), FILTER ( 'Table', [Index] = EARLIER ( [Index] ) - 1 && [SN] = EARLIER ( [SN] ) ) ) VAR _lastdatediff = DATEDIFF ( _lastday, [Date], DAY ) RETURN SWITCH ( TRUE (), [Date] = _mindate, 1, _datediff > 90, IF ( _lastdatediff > 90, 1, 0 ), 0 )Measure2:
_Group = CALCULATE ( SUM ( 'Table'[_Flag] ), FILTER ( ALLSELECTED ( 'Table' ), [Index] <= EARLIER ( [Index] ) ) )Measure3:
_Total value 90 days1 = VAR _value = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), [Index] <= EARLIER ( [Index] ) && [_Group] = EARLIER ( [_Group] ) ) ) VAR _maxindex = CALCULATE ( MAX ( 'Table'[Index] ), FILTER ( 'Table', [_Group] = EARLIER ( [_Group] ) ) ) RETURN IF ( [Index] = _maxindex, _value, BLANK () )Result:
Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
