Forum Discussion
JRParker
Helper III
Helper IIIDAX Formula for Calculating Average Inventory Balance for past 6 months
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
3 Replies
JRParkerBelow 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 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
