Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

exclude current week in datesinperiod/datesbetween calculate

Hi guys,
I need help to get moving average for date interval between last 3 week but not include current week
example : if now is week 30 , moving average value will calculate from w27,w28, and w29

here is my measure :

Sales 3 Rolling Average =
CALCULATE (
AVERAGEX ( VALUES ( vw_stock_avg_sales_weekly[id] ), [Sellout_]),
DATESBETWEEN(vw_stock_avg_sales_weekly[start_date],
DATEADD(LASTDATE(vw_stock_avg_sales_weekly[start_date]),-21,DAY)
,LASTDATE(vw_stock_avg_sales_weekly[start_date])
 
))

any idea to modify this measure so current week not include in calculation?

Thank You



Need Help
  • Fowmy's avatar
    Fowmy
    6 years ago

    aj1973 

    Hi, thanks for pointing it out, it was an oversight from my end. 

    Anonymous 

    Please check the attached file in which I have created a new YEAR+WEEK column based on Start Date. In my first solution, I didn't consider by store or Product, even the ID2 was not correct. 

    https://1drv.ms/u/s!AmoScH5srsIYgYIBbsgVjYpdYMx55w?e=TKbbOb

     

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    I accept KUDOS ☺

    YouTube | LinkedIn

11 Replies

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    You can't nest so many Time Intelligence functions inside CALCULATE!

    Also did you create a Calendar date table? cause in your measure you are using the dates inside the fact tables!

    Can you share a Sample PBIX file! it would be much easier to help

      

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hi aj1973 ,
      Well noted,

      thanks for the information,

      I keep getting wrong value when i use date from calendar table,
      here is the sample pbi file,
      sample_pbi_file 

      I am beginner in using power bi and  would appreciate it if you would help me check and let me know if i miss something,
      Thank You!

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Needed ouput

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    I have attached the file with the calculation for a 3-week average excluding the current week. I modified the Year+Week Column as well.
    Please check and share your feedback. :

    https://1drv.ms/u/s!AmoScH5srsIYgYIAp-uf_MAzQoQ42Q?e=9xanrZ

     



    If you are satisfied with my answer, please mark it as a solution so others can easily find it.

    Don't forget to give KUDOS to replies that help answer your questions


    Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Fowmy ,

      Thank you very much before,
      but i get wrong value when i try to add  dataset with data from different stores and products,
      Are there any suggestions regarding this?
      Heres's  file update dataset:
      update dataset 


      • aj1973's avatar
        aj1973
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous 

        I think your visual is little bit mixed up with duplicate values (Dates and Id2) for different stores

        the formula that Fowmy  is correct in a sens that it does not take care of the duplicate id2 and does not detect which of the id2 goes to what store.

         

        I beleive the id2 column(the filter used in the formula) is not capturing the right number from the id column(Text format), therefore you need to open Power Query and Add column from example and use text after delimter example: 455020191 from the first the row in the column. then change the type to Number. name the added column id2 so you can use it in your formula.

        Then. 

        I suggest that you add slicers to your report to filter by customer_diskripsi and the formula will get you the correct AVERAGEX.

         

        Just to add a note, in this formula 

        3 Week Avg. =
        --CALCULATE(
        AVERAGEX(
        FILTER(
        ALL('Sales data'),
        ('Sales data'[id2]>= MAX('Sales data'[id2] ) - 3) &&
        ('Sales data'[id2]<= MAX ( 'Sales data'[id2] ) - 1)
        ),
        CALCULATE(SUM('Sales data'[Sellout_]))
        )
        --) 
        no need for Calculate as it is not calling for filters other than what was nested inside AVERAGEX