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