Forum Discussion

Gareth_Hunt's avatar
Gareth_Hunt
Icon for Helper II rankHelper II
5 years ago

Average revenue based on Date selection

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  

 

Need Help

6 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Gareth_Hunt
      Icon for Helper II rankHelper II
      5 years ago

      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's avatar
        MattAllington
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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] ) )
        )