Average / YTD Help

[JD2325]

Hi Everyone,

I am really struggling with calculating averages in Power BI and I'm not sure why. I am looking to re-create the following excel graph in Power BI:

I have two tables in my dataset 'Invoice Tracker' & 'Calendar'. I've set the relationship Many to One linking to my 'Invoice Tracker' [DateApproved] column. I also have in my 'Invoice Tracker' table [CostCategory] which are the different accounts, [Subtotal] which is the amount spent.

I'm looking to have multiple bar graphs like above showing the amount spent per account across month/year.What I'm really not sure of is how to get the average or YTD average. Ideally I would have the YTD average show as a consistent value across all months of that year within the data. So for 2022 show a YTD average as a consistent number, and then for 2023 show a different consistent number.

I've tried a few different measures and can't seem to get it right. In the example below I am trying to take my YTD Amount and divide by NumMonths but my NumMonths isn't working the way I would like, and I only want to show the most recent value in my visual.

Most Recent YTD Amount Average =

VAR CurrentYear = YEAR(MAX('Invoice Tracker'[DateApproved]))

VAR LastDateThisYear =

    CALCULATE(

        LASTDATE('Invoice Tracker'[DateApproved]),

        FILTER(

            ALL('Invoice Tracker'[DateApproved]),

            YEAR('Invoice Tracker'[DateApproved]) = CurrentYear

        )

    )

VAR NumMonths = DATEDIFF(DATE(CurrentYear, 1, 1), LastDateThisYear, MONTH) + 1

RETURN

    DIVIDE([YTD Amount], NumMonths)

[Greg_Deckler]

@JD2325 You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Also, you will need this for the average: This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

This might also help: