Compare Sales

[vejas]

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 ?

 

[PaulDBrown]

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

For Sales Change my solution is

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

[PaulDBrown]

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