Average Revenue per Month

[dolevh]

Hi, 

I have these tables: 

Date

1/1/21

1/2/21

1/3/21

1/4/21

1/5/21

1/6/21

1/7/21

1/8/21

1/9/21

1/10/21

1/11/21

1/12/21

Start of Month	CustomerID	Revenue
1/1/21	1	10
1/1/21	2	20
1/1/21	3	30
1/1/21	4	40
1/2/21	1	50
1/2/21	2	60
1/2/21	3	70
1/3/21	1	80
1/3/21	2	90
1/3/21	3	100
1/4/21	1	110
1/4/21	2	120

I'm looking for dax that show me the average revenue per month/period for example: 

01/21 -> (10 + 20 + 30 + 40) / 4 = 25 

02/21 -> (50 + 60 + 70) / 3 = 60 

03/21 -> (80 + 90 + 100) / 3 = 90 

04/21 -> (110 + 120) / 2 = 115 

01/21 + 02/21 = (25 + 60) / 2 = 42.5 

01/21 + 02/21 + 03/21 = (25 + 60 + 90) / 3 = 58.333  

Thanks all!

[v-cazheng-msft · Accepted solution]

Hi @dolevh 

You may try these two Measures.

AvgRevenuePerMon =

CALCULATE (

    AVERAGE ( 'DataTable'[Revenue] ),

    ALLEXCEPT ( 'DataTable', 'DataTable'[Start of Month] )

)

AvgRevenuePrePeriod =

VAR _seldate =

    SELECTEDVALUE ( 'DataTable'[Start of Month] )

VAR min_start_mon =

    CALCULATE ( MIN ( 'DataTable'[Start of Month] ), ALL ( 'DataTable' ) )

VAR _culrevenue =

    SUMX (

        FILTER (

            ALLSELECTED ( 'DataTable'[Start of Month] ),

            'DataTable'[Start of Month] <= _seldate

        ),

        [AvgRevenuePerMon]

    )

VAR _count =

    IF (

        _seldate = min_start_mon,

        0,

        CALCULATE (

            DISTINCTCOUNT ( 'DataTable'[Start of Month] ),

            FILTER ( ALLSELECTED ( 'DataTable' ), 'DataTable'[Start of Month] <= _seldate )

        )

    )

RETURN

    DIVIDE ( _culrevenue, _count )

Then, the result should look like this:

Attached pbix file as a reference.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks a lot!

[Greg_Deckler]

@dolevh Try:

Measure =
  VAR __Table = SUMMARIZE('Table2',[Start of Month],"__Average",AVERAGE('Table2'[Revenue]))
RETURN
  AVERAGEX(__Table,[__Average])

Basically, the measure aggregation pattern. 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.

[dolevh]

it's not true unfortunately 😞