Getting Total of Cumulative column for specfic time period

Question

Hi,

My data requirement is defined in the below excel:

Gross Sales: Getting Directly from DB

Inventory : Calculated column

Inventory Cumulative : Running Total of the "Inventory" column. DAX formula is CALCULATE([Inventory],FILTER(ALL(Calendar),Calendar[Calendar Date] <= MAX(Calendar[Calendar Date])))

Sales Binding Velocity : Last 5 days average of "Gross Sales" column. i.e, Take the summation of last 5 days of "Gross Sales" column and then divide  by 5. DAX formula is

Sales Binding Velocity:= DIVIDE (
  CALCULATE
  (
   [Gross Sales],
   FILTER(
    ALL(Calendar),
    AND(
     Calendar[Calendar Date] <=  MAX(Calendar[Calendar Date]),
     Calendar[Calendar Date] >= (MAX(Calendar[Calendar Date])-4))
               )

  ),
  5)

Average Days of Inventory := Inventory Cumulative/Sales Binding Velocity.

At week level "Average Days of Inventory " should be sum of all the "Average Days of Inventory " divided by 7. At month level it should be sum of all the "Average Days of Inventory " for that month divided by the number of days in that month.

I am getting proper data at day level. But Average Days of Inventory is going wrong at week and month level.

If the granularity is at the week, Inventory cumulative and Sales Binding Velocity is showing up as the values that existed on that weekend date. In the below example, for 2/14/2016  Average Days of Inventory is coming as 480/26 = 18.4.

Where as it should be the summation of that entire week divided by 7.

I guess if I get a solution to get the total of "Inventory Cumulative" and "Sales Binding Velocity" for a specified time period, my issue would be resolve.

Please suggest some solution to this.

---

Raksha

Answer 1

Hi Raksha Hegde,

You'd better create another calculated column to calculate Sales Binding Velocity(7 days average), replace the 5 as 7. 

Sales Binding Velocity:= DIVIDE (
  CALCULATE
  (
   [Gross Sales],
   FILTER( 
    ALL(Calendar), 
    AND(
     Calendar[Calendar Date] <=  MAX(Calendar[Calendar Date]),
     Calendar[Calendar Date] >= (MAX(Calendar[Calendar Date])-6))
               )

  ), 
  7)

In addition, you can use the DATESINPERIOD function, which returns a table that contains a column of dates that begins with the start_date and continues for the specified intervals.

Sales Binding Velocity:= DIVIDE (
  CALCULATE
  (
   SUM[Gross Sales],
     DATESINPERIOD(Calendar[Calendar Date], MAX(Calendar[Calendar Date]),
     -7,day))
  ,7)

If you have any question, please feel free to ask.

Best Regards,
Angelia

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi Angelia,

Thanks for your response.

But in my requirement, Average Days of Inventory should be dynamic.

i.e., a week may or may not have 7 days that particular week. Example, if we consider Dec-2016, first week of December has just 3 days. So in that incase I should be considering sum of (Average Days of Inventory) for these 3 days and then divide by 3.

Also, if the granularity is at month level,  then I should be taking the sum of (Average Days of Inventory)  for that entire month and then divide by the number of days in that month.

Please let me know if this is not clear.

NOTE: Excel file with sample calculate has been uploaded in the below link:
https://1drv.ms/x/s!Ap0Y_wdW3wcmdGlKcx3zX25rCzk

---

Raksha

Answer 3

Might be easier if you use a 4-4-5 Fiscal Calendar
which does not have partial weeks.
http://www.mediafire.com/file/d9mlp2bzeowb4pd/12_15_16.xlsx

Answer 4

Hi Herbert,

Thanks for the response.

But we can't use such "4-4-5 Fiscal Calendar". Because we have other requirements where this "4-4-5 Fiscal Calendar" will not be applicable.

Please let us know if we have any other alternate approach.

Thanks,

Raksha

---

Raksha

Answer 5

Hi Raksha Hegde,

Ok, I got it. Based on your description, if we consider Dec-2016, first week of December has just 3 days. But for 2/14/2016, why the  Average Days of Inventory is coming as 480/26 = 18.4?

Best Regards,
Angelia

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].