locked
Getting Total of Cumulative column for specfic time period RRS feed

  • 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


    • Edited by Raksha Hegde Wednesday, December 14, 2016 12:42 PM
    Wednesday, December 14, 2016 12:28 PM

Answers

  • 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 MSDNFSF@microsoft.com.

    Thursday, December 15, 2016 6:58 AM
  • 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

    Thursday, December 15, 2016 5:53 PM
  • 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 MSDNFSF@microsoft.com.


    Thursday, December 22, 2016 9:50 AM

All replies

  • 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 MSDNFSF@microsoft.com.

    Thursday, December 15, 2016 6:58 AM
  • 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

    Thursday, December 15, 2016 8:51 AM
  • 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

    Thursday, December 15, 2016 5:53 PM
  • 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

    Tuesday, December 20, 2016 6:16 AM
  • 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 MSDNFSF@microsoft.com.


    Thursday, December 22, 2016 9:50 AM