ssas tabular, average of cumulative total

[AntonL]

hi, 

i have a fact table, product movements and start balance on 1st day of month

DateKey

Quantity

i trying to calculate inventory for each day

InvOnDate = CALCULATE(SUM('fact_Move'[Quantity]), DATESMTD(dim_Date[DateID]))

with this calculation all ok

but i want to create a new measure

to receive on month level average of children of InvOnDate

how i can do it with dax? thanx

[amitchandak · Accepted solution]

@AntonL , days Avg at month level

InvOnDate = CALCULATE(averageX(values(dim_date[Date]), CALCULATE(SUM('fact_Move'[Quantity])), DATESMTD(dim_Date[DateID])) )

Month avg at year

InvOnDate = CALCULATE(averageX(values(dim_date[Month Year]), CALCULATE(SUM('fact_Move'[Quantity])), DATESYTD(dim_Date[DateID])) )

[AntonL]

thanks 

it works perfect

may i ask more?

my calendar is until 2023

but fact table is until yeasterday(LastFactDate)

in dim_date i created

LastFactDate:=calculate(max(dim_date [DateID]),dim_date [FlagFactLoaded]=1)

how to stop calculating

InvOnDate = CALCULATE(averageX(values(dim_date[Month Year]), CALCULATE(SUM('fact_Move'[Quantity])), DATESYTD(dim_Date[DateID])) )

on LastFactDate?

thanks in advance