cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
antoniopgouveia
Frequent Visitor

Sum of selected sales values for previous periods

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:

 

ParentIDCreated DateAmountLast AmountLast Amount_Previous Period
006D000000pOj4XIAS03/04/2018 09:469925799257 
006D000000rHnhtIAC03/04/2018 23:57116110116110 
006D000000uHe3xIAC03/04/2018 21:10115000115000 
006D000000xjhG7IAI03/04/2018 11:508000080000 
006D000000yLtscIAC02/04/2018 20:11110000  
006D000000yLtscIAC02/04/2018 20:56113000  
006D000000yLtscIAC02/04/2018 20:56113000113000 
006D000000yLvrnIAC03/04/2018 09:381  
006D000000yLvrnIAC03/04/2018 09:39117300117300 
006D000000yLy1uIAC03/04/2018 22:30   
006D000000yLy1uIAC03/04/2018 22:34115000  
006D000000yLy1uIAC03/04/2018 22:54115000115000 
006D000000yLy3MIAS03/04/2018 22:38100000  
006D000000yLy3MIAS03/04/2018 22:40170100  
006D000000yLy3MIAS03/04/2018 22:43175300  
006D000000yLy3MIAS03/04/2018 22:48184300  
006D000000yLy3MIAS03/04/2018 22:49184525  
006D000000yLy3MIAS03/04/2018 22:50184615  
006D000000yLy3MIAS03/04/2018 22:52184770184770 

 

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

2 REPLIES 2
v-yulgu-msft
Microsoft
Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

 

ParentIDCreated DateAmountSumLast AmountSumLast Amount_Previous Period
006D000000pOj4XIAS03/04/2018 09:469925799257 
006D000000rHnhtIAC03/04/2018 23:57116110116110 
006D000000uHe3xIAC03/04/2018 21:10115000115000 
006D000000xjhG7IAI03/04/2018 11:508000080000 
006D000000yLtscIAC02/04/2018 20:11110000  
006D000000yLtscIAC02/04/2018 20:56113000  
006D000000yLtscIAC02/04/2018 20:56113000113000 
006D000000yLvrnIAC03/04/2018 09:381  
006D000000yLvrnIAC03/04/2018 09:39117300117300 
006D000000yLy1uIAC03/04/2018 22:30   
006D000000yLy1uIAC03/04/2018 22:34115000  
006D000000yLy1uIAC03/04/2018 22:54115000115000 
006D000000yLy3MIAS03/04/2018 22:38100000  
006D000000yLy3MIAS03/04/2018 22:40170100  
006D000000yLy3MIAS03/04/2018 22:43175300  
006D000000yLy3MIAS03/04/2018 22:48184300  
006D000000yLy3MIAS03/04/2018 22:49184525  
006D000000yLy3MIAS03/04/2018 22:50184615  
006D000000yLy3MIAS03/04/2018 22:52184770184770 
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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!