PreviousMonth Calculation

[Anonymous]

Hello,

I have a very simple messure:

TotalSalesBlue = SUMX(FILTER('Sales','Sales'[COLOR]="BLUE"),'Sales'[AMT])

Sales Table is connect with Date Table via DateID.

I'm trying to find Only PreviousMonth, PreviousQuarter Data using Following formula:

PreviousMonthBlueSlaes= CALCULATE ([TotalSalesBlue] , PREVIOUSMONTH(DATE[Date_Id]))

I'm geeting all the months, but I'm only looking for previous month sum.

Thanks in advance.

[Ashish_Mathur]

Hi,

If you modify your TotalSalesBlue measure to this, do you get the correct result.

=CALCULATE(SUM('Sales'[AMT]),'Sales'[COLOR]="BLUE")

[Anonymous]

Even after modifying the result I get all month

1- Changed messured as you suggested, still getting all months as shown below. I'm need to only get 9/30/2018 data.

[v-lili6-msft]

hi, @Anonymous

You may try to this way as below:

Step1:

Add a year month column in date table

Year Month Number = YEAR ( 'Date'[Date] ) * 100 + MONTH ( 'Date'[Date] ) 

Step2:

modify the formula

PreviousMonthBlueSlaes = CALCULATE ([TotalSalesBlue] , PREVIOUSMONTH('Date'[Date]), 'Date'[Year Month Number]=YEAR(TODAY())*100+MONTH(TODAY())-2)

Result:

BeforeAfter

Best Regards,

Lin

[Ashish_Mathur · Accepted solution]

Hi,

Explore relative dates filters