Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
You are welcome.
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