Trailing 3 months are showing wrong values when removing empty records from another column

[vidyasagar159]

Hi All,

I have a product table with prices and dates for year 2017 along with the modified date (Starts from 3rd Quarter) and modified amount. Now my requirement is to create trailing 3 months and subtract the trailing 3 months - modified amount. This was easy when it is in the actual table. But when I am trying to show the data only for the modified date is not blank my trailing 3 Month calculations were going wrong. Not sure where it is going wrong. Can someone please help.

Here is the 3 Months trailing measure expression.

Trailing3months =

var currentdate = MAX(Main[Monthly Price])

var previousdate = DATE(YEAR(currentdate),MONTH(currentdate)-3,DAY(currentdate))

Var Result =

CALCULATE (

SUM(Main[Price]),

FILTER(ALLSELECTED(Main),Main[Monthly Price]>previousdate && Main[Monthly Price] <= currentdate)

)

return

Result

Sample Data:

Main Table
Product	Monthly Price	Price	Trailing 3 Month	Modified date	Modified Amount	Trailing 3 Month - Modified Amount
Apple	05/01/2017	$200
Apple	06/01/2017	$210
Apple	07/01/2017	$196	$606
Apple	08/01/2017	$150	$556	08/01/2017	$50	$506
Apple	09/01/2017	$170	$516	09/01/2017	$30	$486
Apple	10/01/2017	$160	$480	10/01/2017	$40	$440
Apple	11/01/2017	$180	$510	11/01/2017	$20	$490
Apple	12/01/2017	$200	$540	12/01/2017	$10	$530
Expected output Table
Product	Monthly Price	Price	Trailing 3 Month	Modified date	Modified Amount	Trailing 3 Month - Modified Amount
Apple	08/01/2017	$150	$556	08/01/2017	$50	$506
Apple	09/01/2017	$170	$516	09/01/2017	$30	$486
Apple	10/01/2017	$160	$480	10/01/2017	$40	$440
Apple	11/01/2017	$180	$510	11/01/2017	$20	$490
Apple	12/01/2017	$200	$540	12/01/2017	$10	$530

Thanks,

-Vidya

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[vidyasagar159]

@Ashish_Mathur Thank you so much. 

-Vidya

[Ashish_Mathur]

You are welcome.

[amitchandak]

Try using datesinperiod

Make sure you have calendar date table for that

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))  

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601