Sum of selected sales values for previous periods

[antoniopgouveia]

Hi all,

I have a table that records all the changes made to a list of opportunities. These changes can affect the same opportunity (the ParentID identifies each opportunity) at different time points. For example, if an opportunity is created and a certain sales amount is input and later changed to a new value, both modifications would be listed in the table. I have the following sample:

ParentID	Created Date	Amount	Last Amount	Last Amount_Previous Period
006D000000pOj4XIAS	03/04/2018 09:46	99257	99257
006D000000rHnhtIAC	03/04/2018 23:57	116110	116110
006D000000uHe3xIAC	03/04/2018 21:10	115000	115000
006D000000xjhG7IAI	03/04/2018 11:50	80000	80000
006D000000yLtscIAC	02/04/2018 20:11	110000
006D000000yLtscIAC	02/04/2018 20:56	113000
006D000000yLtscIAC	02/04/2018 20:56	113000	113000
006D000000yLvrnIAC	03/04/2018 09:38	1
006D000000yLvrnIAC	03/04/2018 09:39	117300	117300
006D000000yLy1uIAC	03/04/2018 22:30
006D000000yLy1uIAC	03/04/2018 22:34	115000
006D000000yLy1uIAC	03/04/2018 22:54	115000	115000
006D000000yLy3MIAS	03/04/2018 22:38	100000
006D000000yLy3MIAS	03/04/2018 22:40	170100
006D000000yLy3MIAS	03/04/2018 22:43	175300
006D000000yLy3MIAS	03/04/2018 22:48	184300
006D000000yLy3MIAS	03/04/2018 22:49	184525
006D000000yLy3MIAS	03/04/2018 22:50	184615
006D000000yLy3MIAS	03/04/2018 22:52	184770	184770

I have managed to select the last values (as shown in "Last Amount" column) and sum them, given a certain date period selection , by using the following measures:

LastAmount = CALCULATE(VALUES('Table'[Amount]), FILTER('Table', 'Table'[CreatedDate]=MAX('Table'[CreatedDate])))

SumLastAmount = IF (HASONEFILTER (Table'[ParentId]),[LastAmount],
SUMX ( ALLSELECTED ( 'Table'[ParentId]), [LastAmount] )
)

Now, I was trying to to the same but for the previous month of the selected time period. I've tried the following:

SumLastAmount = calculate(IF (HASONEFILTER ( 'Table'[ParentId]),
[LastAmount],
SUMX ( ALLSELECTED ( 'Table'[ParentId]), [LastAmount] )
),PREVIOUSMONTH('Table'[CreatedDate]))

...but it ends in an error where it seems my date column has duplicate values, which is true. I've built a separate calendar, connected to this table through the date fields and changed the formulas accordingly. However, I have no results at all. Any suggestions?

Best regards,

Antonio

[v-yulgu-msft]

Hi @antoniopgouveia,

You said "I've built a separate calendar, connected to this table through the date fields and changed the formulas accordingly. However, I have no results at all", per my understanding, there is no corresponding amount in previous month for each ParentID in above sample table. Maybe that is why the measure returned no results.

What is your desired result for "Last Amount_Previous Period" and "SumLastAmount" ? Could you list your expected results in above table? Besides, which field did you added to slicer?

Regards,

Yuliana Gu

[antoniopgouveia]

Hi @v-yulgu-msft,

I may have not explained it well. I have changed the table to make it easier to undertand. So my LastAmount measure gives me the lastest Amount values and the SumLastAmount sums all of those values for each opportunity ID and this is the measure I'm interested in. See below what the total results are:

ParentID	Created Date	Amount	SumLast Amount	SumLast Amount_Previous Period
006D000000pOj4XIAS	03/04/2018 09:46	99257	99257
006D000000rHnhtIAC	03/04/2018 23:57	116110	116110
006D000000uHe3xIAC	03/04/2018 21:10	115000	115000
006D000000xjhG7IAI	03/04/2018 11:50	80000	80000
006D000000yLtscIAC	02/04/2018 20:11	110000
006D000000yLtscIAC	02/04/2018 20:56	113000
006D000000yLtscIAC	02/04/2018 20:56	113000	113000
006D000000yLvrnIAC	03/04/2018 09:38	1
006D000000yLvrnIAC	03/04/2018 09:39	117300	117300
006D000000yLy1uIAC	03/04/2018 22:30
006D000000yLy1uIAC	03/04/2018 22:34	115000
006D000000yLy1uIAC	03/04/2018 22:54	115000	115000
006D000000yLy3MIAS	03/04/2018 22:38	100000
006D000000yLy3MIAS	03/04/2018 22:40	170100
006D000000yLy3MIAS	03/04/2018 22:43	175300
006D000000yLy3MIAS	03/04/2018 22:48	184300
006D000000yLy3MIAS	03/04/2018 22:49	184525
006D000000yLy3MIAS	03/04/2018 22:50	184615
006D000000yLy3MIAS	03/04/2018 22:52	184770	184770
Total		$   2,277,278.00	$       940,437.00	$                                        967,000.00

This is only a sample containing data for 2 days only. The goal is to get the SumLastAmount per each month, which I'm able to do at the moment (see 1st post), but I can't get the same value for the previous month (which should be 967,000 in this sample - for the 2nd and 3rd March 20018 only). 

The opportunities that the SumLastAmount and SumLastAmount_Previous Period will probably refer to different opportunities and I just want the total value for the previous month. I don't need to have it in the table. I just want to compare the previous value with the current one and measure the increase or decrese. 

I was using the Calendar date on the slice to try to obtain the values for the previous month but the output is always empty. For the current SumLastAmount I'm using the "Creation Date" from the same opportunity table, as it seems to be working fine. 

This what I'm doing to calculate the LastAmount_Previous Period and SumLastAmount_Previous Period:

LastAmount_PP = CALCULATE(VALUES('table'[Amount]), filter('Calendar', 'Calendar'[Date]=max('Calendar'[Date])))

SumLastAmount_PP = CALCULATE(SUMX (ALLSELECTED ( 'table'[ParentId]), [LastAmount_PP]), FILTER(ALL('Calendar'), PREVIOUSMONTH('Calendar'[Date])))

But I get error when I use the measure in a card, saying: "A table of multiple values was supplied where a single was expected." If use the "Created Date" column in the slicer and formulas it will tell me that my dates contain duplicates. 

I'm sure it is just a simple mistake in the formulas that I can't see because I've been looking into this for too long or maybe they're just completely wrong 😛

Any help?

Best regards,

Antonio