Forum Discussion
Delete older data from Table based om reporting month
Hi All,
I have Historical Data coming in every month.
| Report Month | Batch | Date | Sales |
| 1/1/2020 | ABC | 12/20/2019 | 75 |
| 1/1/2020 | ABC | 11/20/2019 | 75 |
| 1/1/2020 | ABC | 10/20/2019 | 200 |
| 1/1/2020 | ABC | 9/11/2019 | 200 |
| 1/1/2020 | ABC | 8/6/2019 | 200 |
| 1/1/2020 | ABC | 7/11/2019 | 75 |
| 1/1/2020 | ABC | 6/5/2019 | 200 |
| 2/1/2020 | ABC | 1/20/2020 | 225 |
| 2/1/2020 | ABC | 12/20/2019 | 225 |
| 2/1/2020 | ABC | 11/20/2019 | 175 |
| 2/1/2020 | ABC | 10/20/2019 | 400 |
| 2/1/2020 | ABC | 9/11/2019 | 100 |
| 2/1/2020 | ABC | 8/6/2019 | 100 |
Based on Report Month -> 01/01/2020 - I want to filter data only for last 3 months Dec, Nov, Oct. and delete data before that using date column.
For 02/01/2020 - Filter Data from Date column for Jan-20, Dec-19, Nov-19 and delete data before that.
Hi Anonymous
Create relationships
Create a measure
Measure = CALCULATE(SUM('Table'[Sales]),FILTER('Table',DATEDIFF('Table'[Date],[Report Month],MONTH)>=1&&DATEDIFF('Table'[Date],[Report Month],MONTH)<=3))Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- az38
Community Champion
Hi Anonymous
for example try to create a table
New Table = FILTER(ALL(Table), DATEDIFF(Table[Date], Table[Report Month], MONTH) <= 3 ) 
v-juanli-msftCommunity Support
Hi Anonymous
Create relationships
Create a measure
Measure = CALCULATE(SUM('Table'[Sales]),FILTER('Table',DATEDIFF('Table'[Date],[Report Month],MONTH)>=1&&DATEDIFF('Table'[Date],[Report Month],MONTH)<=3))Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
