Forum Discussion
Helper IIAverage revenue based on Date selection
Community ChampionYour 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]))
Helper IIHi 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
- 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] ) ) )