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 Previous Year = CALCULATE([Sales Total],SAMEPERIODLASTYEAR('Calendar'[Date]))

Sales Change = [Sales Total]-[Sales Previous Year]
Sales Change % = DIVIDE([Sales Total]-[Sales Previous Year];[Sales Previous Year])

Sales YTD = CALCULATE([Sales Total];DATESYTD('Calendar'[Date]))

I have no any slicers or any additional filters. 

I just want to see comparable periods - green border table without red bordered measures. How measures should be modified ?

 

 

Need Help

3 Replies

  • 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())