Forum Discussion

JRParker's avatar
JRParker
Icon for Helper III rankHelper III
3 years ago

DAX 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"     ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
JRParker
Icon for Helper III rankHelper III
3 years ago

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 & YearMy Inv TotalYour Inv TotalInventory 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