TheStressMachine
New Member
- Joined
- May 19, 2016
- Messages
- 9
Hi,
I've taken the logic from this post (and other similar ones):
excel formula - DAX / PowerPivot query functions to spread aggregated values over time period - Stack Overflow
To create a pivot table with an even spread of revenue over time.
In my data table (CombinedOutput), I have 3 key fields:
StartDate
EndDate
RevenuePerDay (I created this in the source data trying to solve my performance problem, the post above does this calculation in DAX).
I also have a typical calendar table, unconnected per the post.
I'm trying to spread revenue per day across any slice of time (months, quarters, years, etc) and have two measures:
Measure1:
CALCULATE(SUM(CombinedOutput[RevPerDay]),
FILTER (
CombinedOutput,
CombinedOutput[StartDate] <= MAX ( Calendar[Date] )
&&CombinedOutput[EndDate] >= MAX (Calendar[Date] )
)
)
Measure2:
SUMX (
VALUES ( Calendar[Date] ),
SUMX ( VALUES (CombinedOutput ), [Measure1] )
)
This works, but I'm running into performance problems. The entire dataset takes about a minute to calculate and playing with various slicers to filter the data takes about 10 seconds a pop. My data table has about 7,000 rows and my calendar table has about 450. Adding Measure1 to the pivot doesn't cause a performance problem (but is obviously the wrong data), adding measure2 when measure1 doesn't filter against the calendar table isn't a performance problem either (but the spread goes on forever and ignores start/end). It seems that the double pass over the calendar table is causing the issues and I'm not a real data guy, just an example junkie, so I'm not sure if there is a more efficient way to achieve the same goal.
I would have to scramble the dataset before sharing so let me know if that's needed.
Any ideas on how to do this more efficiently? I would be a sad dude if I can't make this happen with a relatively small data set.
I've taken the logic from this post (and other similar ones):
excel formula - DAX / PowerPivot query functions to spread aggregated values over time period - Stack Overflow
To create a pivot table with an even spread of revenue over time.
In my data table (CombinedOutput), I have 3 key fields:
StartDate
EndDate
RevenuePerDay (I created this in the source data trying to solve my performance problem, the post above does this calculation in DAX).
I also have a typical calendar table, unconnected per the post.
I'm trying to spread revenue per day across any slice of time (months, quarters, years, etc) and have two measures:
Measure1:
CALCULATE(SUM(CombinedOutput[RevPerDay]),
FILTER (
CombinedOutput,
CombinedOutput[StartDate] <= MAX ( Calendar[Date] )
&&CombinedOutput[EndDate] >= MAX (Calendar[Date] )
)
)
Measure2:
SUMX (
VALUES ( Calendar[Date] ),
SUMX ( VALUES (CombinedOutput ), [Measure1] )
)
This works, but I'm running into performance problems. The entire dataset takes about a minute to calculate and playing with various slicers to filter the data takes about 10 seconds a pop. My data table has about 7,000 rows and my calendar table has about 450. Adding Measure1 to the pivot doesn't cause a performance problem (but is obviously the wrong data), adding measure2 when measure1 doesn't filter against the calendar table isn't a performance problem either (but the spread goes on forever and ignores start/end). It seems that the double pass over the calendar table is causing the issues and I'm not a real data guy, just an example junkie, so I'm not sure if there is a more efficient way to achieve the same goal.
I would have to scramble the dataset before sharing so let me know if that's needed.
Any ideas on how to do this more efficiently? I would be a sad dude if I can't make this happen with a relatively small data set.
