Finding the sum of a column based on previous date

[Anonymous]

Hello, 

I am looking to return an aggregate of values based on the month it is in. I have a 'DateAssigned' Column that is not continuous and a Calendar'Date' column that is continuous. 

DateAssigned TrailersAssignedTotalRoutesCompletedCalibrationsReceivingTempsVerified

10/2/2022	19	20	1	1
10/3/2022	11	18	1	1
10/4/2022	21	21	1	1
10/5/2022	15	20	1	1
10/6/2022	21	21	1	1
10/7/2022	0	3	1	1
10/9/2022	17	17	1	1
10/10/2022	18	18	1	1
10/11/2022	21	21	1	1
10/12/2022	19	20	1	1
10/13/2022	17	22	1	1
10/14/2022	3	3	1	1
10/16/2022	17	17	1	1
10/17/2022	18	18	1	1
10/18/2022	21	21	1	1
10/19/2022	17	21	1	1
10/20/2022	21	21	1	1
10/21/2022	0	3	1	1
10/23/2022	0	17	1	1
10/24/2022	17	18	1	1
10/25/2022	20	21	1	1
10/26/2022	1	20	1	1
10/27/2022	17	21	1	1
10/28/2022	3	3	1	1
10/30/2022	17	17	1	1
10/31/2022	18	18	1	1
11/1/2022	21	21	1
11/2/2022	20	20	1
11/3/2022	14	21	1
11/4/2022	3	3	1
11/6/2022	0	17	1
11/7/2022	18	18	1
11/8/2022	16	21	1
11/9/2022	17	20	1
11/10/2022	21	21	1
11/11/2022	3	3	1
11/13/2022	17	17
11/14/2022	18	18
11/15/2022	21	21
11/16/2022	19	20
11/17/2022		29

I would like to return the sum of 'Completed Calibrations' only in the previous month (October). I would like for this to be dynamic so when it's December I can see the data for November.

Thank you,

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to calcualte the sum of [Completed Calibrations] in the last month.

Here are the steps you can refer to:

(1)My test data is the same as yours.

(2)We can create a measure :

Last Month value = var _currrent_date = MAX('Table'[DateAssigned])
var _t =FILTER(ALLSELECTED('Table') , MONTH('Table'[DateAssigned]) = MONTH(_currrent_date)-1)
return
SUMX(_t , [CompletedCalibrations])

(3)Then we can put the date and this measure in the viusal and we will meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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