Need Help With Time-Series Measures: One and Two Months Prior Sales Based on Selected Value

[paulsnet1986]

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

[Ritaf1983 · Accepted solution]

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))+0

3. Two Month Prior = CALCULATE([current month margin],DATEADD('Table'[Invoice],-2,MONTH))+0

Link to the sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

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))+0

3. Two Month Prior = CALCULATE([current month margin],DATEADD('Table'[Invoice],-2,MONTH))+0

Link to the sample file 

If 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 · Accepted solution]

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))+0

 Two Month Prior = CALCULATE([current month margin],DATEADD('Dates_table'[Date],-2,MONTH))+0

-  Use the date from the date table in the slicer and your visual

I recorded the screens of these actions You can download the mp4 from: Here 
And link to a new pbix is : Here

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

@paulsnet1986

[paulsnet1986]

That solved it! @Ritaf1983 THANK YOU SO MUCH!

[Ritaf1983]

Glad to help 🙂