Average revenue based on Date selection

[Gareth_Hunt]

HI Guys, 

would appreciate some insight o nhow best to set this up, I have a Calander table (DCalander[date]) and a seperate table containing invoices (transactional data[invoice]) related by date.

I have a Date filter in years at the top of the report, the user can select any number of years as an analysis period (2,3,4 years for example).

I have a Measure for Year Selected to return the revenue value during that period 

YS-Revenue = CALCULATE(SUM('Transactional data'[invoive]),DATEADD('DCalandar'[Date], 0, year))

this way I can return a revenue for any year or combination of years selected.

now what  I would like to do is be able to do is for any time period calculate the last year selected revenue, not the for the full period of the selection.

Having done that I would also like to calculate the average revenue for all other years excluding last year selected all driven from the year filter list 

Thanks guys  

[MattAllington]

Your first formula is overly complex. This will work

YS-Revenue = SUM('Transactional data'[invoive])

as for the other 2, assuming your calendar table has a year column and that is what is in your slicer

Revenue Last selected year = CALCULATE(SUM('Transactional data'[invoive]),All(calendar),calendar[year]=max(calendar[year]))

Avg yearly Revenue = averagex(values(calendar[year]),CALCULATE(SUM('Transactional data'[invoive]))

[Gareth_Hunt]

Hi Matt,

thank you for coming bac kto me on this one,

so I took your example expression and input it into my model, but  I get this error

nb replaces BLIV70- invoice revenue

As for the second answer, this would return an average for all years seleected I want to exclude hte last year from the average 

appreciate your feedback thanks!

Gareth

[MattAllington]

sorry for the error.  that's what happens when you write DAX on an ipad

Revenue Last selected year =
CALCULATE (
SUM ( 'Transactional data'[invoice] ),
FILTER ( ALL ( calendar ), calendar[year] = MAX ( calendar[year] ) )
)

Note above:  ALL(Calendar[Year]) is more efficient than ALL(Calendar) but I don't know if it will work for you given you haven't shared what else you are doing.  You should try using that - it if works, it is better.

Avg yearly Revenue =
AVERAGEX (
CALCULATETABLE (
VALUES ( calendar[year] ),
FILTER ( ALL ( calendar[year] ), calendar[year] <> MAX ( calendar[year] ) )
),
CALCULATE ( SUM ( 'Transactional data'[invoice] ) )
)

[Gareth_Hunt]

Hi Matt, 

great thank you for the first element that seems towork with either FILTER(ALL(Dcalander) or FILTER(ALL(Dcalander[year] so will go with your recommendation on that one thank you.

The average calculation doesnt look to be giving me the expected answers though, maybe  I could ask you to help me further on this one. 

taking your example and applying to my applciation i have the followiny

Avg yearly Revenue =

AVERAGEX (

CALCULATETABLE (

VALUES ( DCalandar[Year]),

FILTER ( ALL ( DCalandar), DCalandar[Year] <> MAX (DCalandar[Year]))

),

CALCULATE ( SUM ( 'Transactional data'[BLIV70] ) )

)

If i was to consider 2 full years of data, 2018 =56k revenue, 2019-54K revenue - I would expect the average to be 55K revenue but  I return a value of 35K revenue as an average using the above?

Gareth

[v-lionel-msft]

Hi @Gareth_Hunt ,

Or Like this? Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[MattAllington]

mmm, sorry again :-).  These are not my best answers 🙂

I forgot about the slicer.  It is easiest to write the formulas in teh model and test - that is how to flush out mistakes.  Of course, you are doing the testing becuase I don't have the model.  The issue is that my formula removes all filters and then reapplies all but the last year, but that is not actually what is needed.  We need to keep all the items selected in the slicer, then keep all but the last selected year.  Slightly different.

I think this will work, but as before, I haven't tested it because I don't have the model.

Avg yearly Revenue =
VAR SelectedYears =
    ALLSELECTED ( Calendar[Year] )
VAR LastSelectedYear =
    MAX ( Calendar[Year] )
VAR YearsForAverage =
    CALCULATETABLE (
        VALUES ( Calendar[Year] ),
        SelectedYears,
        Calendar[Year] <> LastSelectedYear
    )
RETURN
    AVERAGEX ( YearsForAverage, CALCULATE ( SUM ( 'Transactional data'[BLIV70] ) ) )