Forum Discussion
Helper INeed Help With Time-Series Measures: One and Two Months Prior Sales Based on Selected Value
Hi, I am really struggling with this one: I need to create a margin report by product in that, when a given month is selected, the measures will give me the sum of the margins for each product for them month in one column, the sum of the margin for that product's prior month in the next column, and the sum of the margin for that product's from two months prior in the last column.
Any help would be great appreciated!
Table
| Invoice | Product | Margin |
| 6/1/2022 | 1111 | 100 |
| 6/1/2022 | 2222 | 20 |
| 8/1/2019 | 1111 | 50 |
| 8/1/2019 | 1111 | 100 |
| 8/1/2019 | 2222 | 200 |
| 7/1/2019 | 1111 | 50 |
| 7/1/2019 | 1111 | 200 |
| 7/1/2019 | 2222 | 100 |
| 6/1/2019 | 1111 | 25 |
| 6/1/2019 | 1111 | 25 |
| 6/1/2019 | 1111 | 50 |
Desired output when "08/01/2019" is selected in a slicer/dropdown
| Product | Selected Month Margin | One Month Prior Margin | Two Month Prior Margin |
| 1111 | $150.00 | $250.00 | $100.00 |
| 2222 | $200.00 | $100.00 | $0.00 |
Hi paulsnet1986
You can use these measures :
1. current month margin = sum('Table'[Margin])2.One Month Prior = CALCULATE([current month margin],DATEADD('Table'[Invoice],-1,MONTH))+03. Two Month Prior = CALCULATE([current month margin],DATEADD('Table'[Invoice],-2,MONTH))+0If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi 1. Try to check your steps near the file I linked in the first reply.
2. If it still not working:
- Create a date table.- Create a relationship between it and your invoices table
- Mark this table as a date table
- Use this table for the formulas modify them to :
current month margin = sum('Table'[Margin])One Month Prior = CALCULATE([current month margin],DATEADD('Dates_table'[Date],-1,MONTH))+0Two Month Prior = CALCULATE([current month margin],DATEADD('Dates_table'[Date],-2,MONTH))+0
5 Replies
- Ritaf1983
Super User
Hi paulsnet1986
You can use these measures :
1. current month margin = sum('Table'[Margin])2.One Month Prior = CALCULATE([current month margin],DATEADD('Table'[Invoice],-1,MONTH))+03. Two Month Prior = CALCULATE([current month margin],DATEADD('Table'[Invoice],-2,MONTH))+0If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
- paulsnet1986
Hi Ritaf1983
Thanks for your reply! I am getting the following error FUNCTION 'DATEADD" expects contiguous selection when the date column is not unique, has gaps, or contains a time portion.- Ritaf1983
Hi 1. Try to check your steps near the file I linked in the first reply.
2. If it still not working:
- Create a date table.- Create a relationship between it and your invoices table
- Mark this table as a date table
- Use this table for the formulas modify them to :
current month margin = sum('Table'[Margin])One Month Prior = CALCULATE([current month margin],DATEADD('Dates_table'[Date],-1,MONTH))+0Two Month Prior = CALCULATE([current month margin],DATEADD('Dates_table'[Date],-2,MONTH))+0
