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 need to create measure to calculate cumulative sum of quantity field which always shows value for the end of selected period.
Source data is like this:
OpportunityId | DateKey | Quantity |
1 | 20191101 | 2 |
1 | 20191215 | -1 |
1 | 20200129 | 0 |
1 | 20200130 | 0 |
1 | 20200131 | 1 |
2 | 20200101 | 0 |
2 | 20200102 | 0 |
2 | 20200103 | 1 |
There are quantity values by date, but some date might be missing.
Output of my report should be like this(instead of month could be year, quarter, or any other related non-date dimension - in this case it's should be total for whole table):
Month | Quantity |
Nov 2019 | 2 |
Dec 2019 | 1 |
Jan 2020 | 3 |
To do this I have column which calculates running total for each opportunity:
TotalQuantity = CALCULATE(
SUM('Opportunity Daily Snapshot'[Quantity]),
ALL('Opportunity Daily Snapshot'),
'Opportunity Daily Snapshot'[DateKey]<=EARLIER('Opportunity Daily Snapshot'[DateKey]),
'Opportunity Daily Snapshot'[OpportunityId]=EARLIER('Opportunity Daily Snapshot'[OpportunityId])
)
Result:
OpportunityId | DateKey | Quantity | TotalQuantity |
1 | 20191101 | 2 | 2 |
1 | 20191215 | -1 | 1 |
1 | 20200129 | 0 | 1 |
1 | 20200130 | 0 | 1 |
1 | 20200131 | 1 | 2 |
2 | 20200101 | 0 | 0 |
2 | 20200102 | 0 | 0 |
2 | 20200103 | 1 | 1 |
Now I need some measure/column to calculate sum(TotalQuntity) by Month, using last TotalQuantity value in each month.
I tried to do this in measure:
Test:=
CALCULATE (
SUM (TotalQuantity ),
FILTER ( ALL ( 'Opportunity Daily Snapshot'), 'Opportunity Daily Snapshot'[DateKey] = MAX ('Opportunity Daily Snapshot'[DateKey]) ),
VALUES ('Opportunity Daily Snapshot'[OpportunityId])
)
But problem, is that some opportunities don't have any values on the last day of month, which is returned by MAX ('Opportunity Daily Snapshot'[DateKey]).
Is there any way to filter by max(Datekey) for particular OpportunityId in the selected period(Month, Quarter etc)?
Or is there any other way to get only latest row for each OpportunityId in current selection?
@cyberirbis - Did you get a resolution? If so, please mark as answer, otherwise, @ me.
Hi, I've tried this, but it doesn't resolve my particular issue.
Finally I fixed it by changing source table. I added records on each day for each OpportunityId. It return correct values, but in some cases query fails fith out of memory error
@cyberirbis , try like
sumx(summarize(table,table[OpportunityId],"_1" ,lastnonblankvalue(table[DateKey],sum(Table[Quantity]))),[_1])
@cyberirbis - This is basically Lookup Min/Max if I understand correctly.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |