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))returnResult Sample Data:Main TableProduct Monthly Price PriceTrailing 3 MonthModified dateModified AmountTrailing 3 Month - Modified AmountApple05/01/2017$200 Apple06/01/2017$210 Apple07/01/2017$196$606 Apple08/01/2017$150$55608/01/2017$50$506Apple09/01/2017$170$51609/01/2017$30$486Apple10/01/2017$160$48010/01/2017$40$440Apple11/01/2017$180$51011/01/2017$20$490Apple12/01/2017$200$54012/01/2017$10$530 Expected output TableProduct Monthly Price PriceTrailing 3 MonthModified dateModified AmountTrailing 3 Month - Modified AmountApple08/01/2017$150$55608/01/2017$50$506Apple09/01/2017$170$51609/01/2017$30$486Apple10/01/2017$160$48010/01/2017$40$440Apple11/01/2017$180$51011/01/2017$20$490Apple12/01/2017$200$54012/01/2017$10$530 Thanks,-Vidya

Hi, You may download my PBI file from here. Hope this helps.

vidyasagar159

Helper II

6 years ago

Solved

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

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

Need Help

Ashish_Mathur
6 years ago
Hi,

You may download my PBI file from here.

Hope this helps.

Forum Discussion

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

4 Replies

Related content

Remove trailing commas

Remove trailing spaces

Fabric Free Trail

Remove trailing 2 characters from end string

Microsoft Fabric trail