Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |