DAX Formula for Calculating Average Inventory Balance for past 6 months

[JRParker]

Currently measure to calculate Monthly Total Inventory 

SUMX(

    FILTER(

        'BS_Data',

        'BS_Data'[Sub-Category] = "Inventory" || 'BS_Data'[Sub-Category] = "Inventory Reserves"

    ),

    'BS_Data'[Balance]

)

Need measure to calculate the average inventory for the past six months, bearing in mind the first five months don't have six months history (but the AVERAGE(X) functions should recognize this).  ....

The BS_Data table (as used in the measure above) includes a Date column and is related to a Date table which itself includes a DateInt column of Type Whole Number with the following format example for June 30, 2023: 20230630

[Anonymous]

Hi @JRParker 

You can refer to the following sample.

Date table

The data table is the same as yours.

I modified your total measure, and create a average measure

Total = SUMX(
    FILTER(
        ALLSELECTED('BS_Data'),
        OR('BS_Data'[Sub-Category] = "Inventory" , 'BS_Data'[Sub-Category] = "Inventory Reserves")&&[Date] in VALUES(BS_Data[Date]))
    ,
    'BS_Data'[Balance]
)

Average =
VAR a =
    CALCULATE (
        [Total],
        DATESINPERIOD ( 'Date'[Date], SELECTEDVALUE ( 'Date'[Date] ), -6, MONTH )
    )
VAR b =
    CALCULATE (
        DISTINCTCOUNT ( 'BS_Data'[Date] ),
        DATESINPERIOD ( 'Date'[Date], SELECTEDVALUE ( 'Date'[Date] ), -6, MONTH )
    )
RETURN
    IF ( [Total] <> BLANK (), DIVIDE ( a, b ) )

Output

Best Regards!

Yolo Zhu

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

[JRParker]

thank you v-xinruzhu-msft .....results below.  The [Month & Year] field is part of the DATE table related to the BS_Data via [Date] field.  There is an issue with context I likely haven't provided:

Month & Year	My Inv Total	Your Inv Total	Inventory Average
Feb-22	$1,792,922.31	$1,792,922.31	$1,792,922.31
Mar-22	$2,014,764.71	$2,014,764.71	$1,792,922.31
Apr-22	$2,056,898.05	$2,056,898.05	$1,792,922.31
May-22	$1,970,625.12	$1,970,625.12	$1,792,922.31
Jun-22	$1,991,253.61	$1,991,253.61	$1,792,922.31
Jul-22	$1,953,782.03	$1,953,782.03	$1,792,922.31
Aug-22	$2,191,453.59	$2,191,453.59	$1,792,922.31
Sep-22	$2,310,363.95	$2,310,363.95	$1,792,922.31
Oct-22	$2,658,989.56	$2,658,989.56	$1,792,922.31
Nov-22	$2,788,789.25	$2,788,789.25	$1,792,922.31
Dec-22	$2,765,791.27	$2,765,791.27	$1,792,922.31
Jan-23	$2,924,794.48	$2,924,794.48	$1,792,922.31
Feb-23	$3,036,040.05	$3,036,040.05	$1,792,922.31
Mar-23	$3,108,012.60	$3,108,012.60	$1,792,922.31
Apr-23	$2,905,068.30	$2,905,068.30	$1,792,922.31
May-23	$2,752,525.03	$2,752,525.03	$1,792,922.31
Jun-23	$2,708,054.29	$2,708,054.29	$1,792,922.31

[JRParker]

Below data includes Total inventory for above measure and Average calculation:

Sub-Category	Date	Account	Balance	Total	Average
Inventory	2/28/2022	1000	$173,755.36
Inventory	2/28/2022	2000	$2,396,491.26
Inventory Reserves	2/28/2022	3000	($727,324.31)	$  1,842,922.31	$  1,842,922.31
Inventory	3/31/2022	4000	($16,139.78)
Inventory	3/31/2022	1000	$127,339.02
Inventory	3/31/2022	5000	$393.89
Inventory	3/31/2022	2000	$2,464,234.37
Inventory Reserves	3/31/2022	3000	($727,324.32)	$  1,848,503.18	$  1,845,712.75
Inventory	4/30/2022	4000	$3,984.03
Inventory	4/30/2022	1000	$45,977.16
Inventory	4/30/2022	5000	$5,752.73
Inventory	4/30/2022	2000	$2,612,699.21
Inventory Reserves	4/30/2022	3000	($727,324.32)	$  1,941,088.81	$  1,877,504.77
Inventory	5/31/2022	1000	$23,127.14
Inventory	5/31/2022	2000	$2,543,720.99
Inventory Reserves	5/31/2022	3000	($724,651.48)	$  1,842,196.65	$  1,868,677.74
Inventory	6/30/2022	1000	$58,808.53
Inventory	6/30/2022	2000	$2,542,381.53
Inventory Reserves	6/30/2022	3000	($721,131.09)	$  1,880,058.97	$  1,870,953.98
Inventory	7/31/2022	1000	$80,969.48
Inventory	7/31/2022	2000	$2,471,854.94
Inventory Reserves	7/31/2022	3000	($730,192.13)	$  1,822,632.29	$  1,862,900.37
Inventory	8/31/2022	1000	$120,011.34
Inventory	8/31/2022	2000	$2,655,650.40
Inventory Reserves	8/31/2022	3000	($806,739.13)	$  1,968,922.61	$  1,883,900.42
Inventory	9/30/2022	1000	$31,772.13
Inventory	9/30/2022	2000	$2,769,393.21
Inventory Reserves	9/30/2022	3000	($743,358.78)	$  2,057,806.56	$  1,918,784.32
Inventory	10/31/2022	1000	$59,120.05
Inventory	10/31/2022	2000	$3,064,282.38
Inventory Reserves	10/31/2022	3000	($744,559.05)	$  2,378,843.38	$  1,991,743.41
Inventory	11/30/2022	1000	$62,197.02
Inventory	11/30/2022	2000	$3,164,264.98
Inventory Reserves	11/30/2022	3000	($793,079.90)	$  2,433,382.10	$  2,090,274.32
Inventory	12/31/2022	1000	$74,531.77
Inventory	12/31/2022	2000	$3,143,180.69
Inventory Reserves	12/31/2022	3000	($792,709.63)	$  2,425,002.83	$  2,181,098.30
Inventory	1/31/2023	1000	$49,807.43
Inventory	1/31/2023	2000	$3,317,395.47
Inventory Reserves	1/31/2023	3000	($794,295.07)	$  2,572,907.83	$  2,306,144.22
Inventory	2/28/2023	4000	($1.53)
Inventory	2/28/2023	1000	$19,876.13
Inventory	2/28/2023	2000	$3,459,801.51
Inventory Reserves	2/28/2023	3000	($798,473.36)	$  2,681,202.75	$  2,424,857.58
Inventory	3/31/2023	1000	$37,662.19
Inventory	3/31/2023	2000	$3,472,822.01
Inventory Reserves	3/31/2023	3000	($767,511.49)	$  2,742,972.71	$  2,571,093.64
Inventory	4/30/2023	1000	$14,343.54
Inventory	4/30/2023	2000	$3,303,697.47
Inventory Reserves	4/30/2023	3000	($757,092.97)	$  2,560,948.04	$  2,569,402.71
Inventory	5/31/2023	1000	$15,891.52
Inventory	5/31/2023	2000	$3,202,456.96
Inventory Reserves	5/31/2023	3000	($762,636.58)	$  2,455,711.90	$  2,573,124.34
Inventory	6/30/2023	1000	$35,325.97
Inventory	6/30/2023	2000	$3,131,172.31
Inventory Reserves	6/30/2023	3000	($790,504.02)	$  2,375,994.26	$  2,564,956.25