Delete older data from Table based om reporting month

[Anonymous]

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.

[v-juanli-msft · Accepted solution]

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.

[az38]

Hi @Anonymous 

for example try to create a table

New Table = FILTER(ALL(Table),
DATEDIFF(Table[Date], Table[Report Month], MONTH) <= 3 )