Forum Discussion

vejas's avatar
vejas
Helper I
8 years ago

Compare Sales

  Hello ,      I want to compare sales from different periods. This is my table     My Calendar Table -  2016-01-01 till 2018-12-31 Measures: Sales Total = SUM(Sales[Amount]) Sales Pre...
Need Help
PaulDBrown's avatar
PaulDBrown
Community Champion
8 years ago

vejas

 

you need to create the measures wrapped in a CALCULATE function which filters the year and relevant months.. 

 

for example, for your measure sales change, create a new measure as follows:

 

sales change for green selection = CALCULATE ([sales change],

FILTER(‘calendar’, ‘

calendar’ [year] =YEAR(TODAY())-1

|| ‘calendar’[year] = YEAR(TODAY()) && ‘calendar’[month] < MONTH( TODAY())

)

)

 

These will make the filters dinamic. If you want the month and year filters static, just substitute the corresponding DAX code with the relevant years and months (2107, 2018 and March)

  • vejas's avatar
    vejas
    Helper I
    8 years ago

    For Sales Change my solution is

     

    Sales Change = IF(OR(ISBLANK([Sales Total]);ISBLANK([Sales Previous Year]));BLANK();[Sales Total]-[Sales Previous Year])

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      8 years ago

      What I’m saying is that if you use the measure I posted for each of your measures, the table will only display the values you,ve circled in green.

       

      EDIT: Actually, that might filter out the previous years values for 2017. Try the following with the measures:

       

      <<New Measures>>  = IF(OR(calendar[year]= YEAR(TODAY()) - 1 , calendar[year] = YEAR(TODAY())  && calendar[month] < MONTH(TODAY())),  [<<YOUR OLD MEASURE>>], BLANK())