Forum Discussion
Calculate Dynamic Velocity based on the slicer selected
Hi Ktrain
1.Create a calendar date table
Table = CALENDAR(DATE(2018,1,1),DATE(2018,12,31))
then in this table, create columns and measures
columns, then add this column to the sliceron the report.
calendar month = MONTH([Date])
Measures
min month = MIN('Table'[calendar month]) max month = MAX([calendar month])2.create relationships between two tables
3. create measures in the data table
Assume data table is like this
sum = CALCULATE ( SUM ( Sheet6[velocity] ), FILTER ( ALLEXCEPT ( Sheet6, Sheet6[skus] ), [month] >= [min month] && [month] <= [max month] ) )
numbers fo month =
CALCULATE (
COUNT ( Sheet6[month] ),
FILTER (
ALLEXCEPT ( Sheet6, Sheet6[skus] ),
[month] >= [min month]
&& [month] <= [max month]
)
)
aveage = [sum]/[numbers fo month]Best Regards
Maggie
Community SupportHi Ktrain
I can't see "calendar date table" from your screenshot, how do you create the [Min month] or [Max month]?
Best Regards
maggie
I did made the calendar table, but it is not work. I removed it, and make the [Min Month], [Max Month] and Month column in the Order Line Data.
I created [Min Month] and [Max Month] as mesasure.
Thank you.
Hi Maggie v-juanli-msft,
I found why the calendar is not work due to the receveing date from my data has date and time. I edit to date format only. Now it is working . However, my omputer can't handle the sum calulation, due to large data from. I am still waiting for swapping out new computer will more memory. Hopefuly, I will see result once I have new computer.
Thank you.