Cumulative average per day in power bi

[Bambang_Sono]

Please ask, I want to make a table to find the cumulative average tonnage based on date and month in Power BI, like the following picture:

and this is the dax formula I can't get:
Release Average = CALCULATE(
AVERAGE(DataRelease[TONAGE]),
DataRelease[Date] = EARLIER(DataRelease[Date]))

[v-heq-msft · Accepted solution]

Hi @Bambang_Sono ,
Thanks for ryan_mayu and bhanu_gautam reply.

You can also try the follwing code to create a calculate column

Avg per days = 
VAR CurrentDate = 'Table'[Date]
VAR CurrentMonth = 'Table'[Month]
VAR CurrentTonase = 'Table'[Tonase]
VAR DaysUntilCurrentDate = COUNTROWS(FILTER('Table', 'Table'[Month] = CurrentMonth && 'Table'[Date] <= CurrentDate))
VAR CumulativeTonase = 
    CALCULATE(
        SUM('Table'[Tonase]),
        FILTER(
            'Table',
            'Table'[Month] = CurrentMonth &&
            'Table'[Date] <= CurrentDate
        )
    )
RETURN
    CumulativeTonase / DaysUntilCurrentDate

Final output

Best regards,
Albert He

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

[ryan_mayu]

@Bambang_Sono 

you can try this

Column = AVERAGEX(FILTER('Table','Table'[Month]=EARLIER('Table'[Month])&&'Table'[Date]<=EARLIER('Table'[Date])),'Table'[Tonase])

pls see the attachment below

[bhanu_gautam]

@Bambang_Sono , Try using

DAX
Release Average =
VAR CurrentDate = DataRelease[Date]
RETURN
CALCULATE(
AVERAGE(DataRelease[TONAGE]),
FILTER(
ALL(DataRelease),
DataRelease[Date] <= CurrentDate
)
)

[Bambang_Sono]

What if it's combined with the summarize function?

example of my dax formula:
Average Release =
VAR CurrentDate = DataRelease[Date].[Date]
RETURN
CALCULATE(
AVERAGEX(DataRelease[TONAGE]),
SUMMARIZE(
FILTER(
ALL(DataRelease),DataRelease[Date].[Date] <= CurrentDate)
)