Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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



1 ACCEPTED SOLUTION

@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

Fowmy_0-1595227414038.png

 

 

________________________

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

I accept KUDOS ☺

YouTube | LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

11 REPLIES 11
Fowmy
Super User
Super User

@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

 

Fowmy_0-1595175100794.png

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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
Community Champion
Community Champion

Hi @Anonymous 

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

Use filter.PNG

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.

Use filter 2.PNG

 

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
 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

Hi @aj1973 ,
Thanks for the information ,
for your information when i tried to filter with slicer it give the wrong value

haniya_azzahra_0-1595218365121.png

but when i tried to filter in power query it gives the right value

haniya_azzahra_1-1595218417389.png

and data's id is (customer id&product_id&year&week),
meanwhile id 2 is (year &week),
regarding to this how can i make average value based on id and get date  filter last 3 weeks?
before i've tried with this measure where averagex is based on id 

Sales 3 week Rolling Average =
CALCULATE (
AVERAGEX ( VALUES ( vw_stock_avg_sales_weekly[id] ), [Sellout_]),
DATESINPERIOD (vw_stock_avg_sales_weekly[start_date],MAX(vw_stock_avg_sales_weekly[start_date]),-21,DAY)
)
 

and output result are correct but the time period still contains the current week,
is there any suggestion to change interval/workaround logic for interval in datesinperiod?

 

Anonymous
Not applicable

haniya_azzahra_0-1595219204626.png

the output result from measure

@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

Fowmy_0-1595227414038.png

 

 

________________________

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

I accept KUDOS ☺

YouTube | LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

aj1973
Community Champion
Community Champion

@Fowmy  Welcome,

@Anonymous Glad i helped both of you to get the solution. 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

Hi @Fowmy 
it works!
I’m so grateful for your help. I was really stuck with this
Thank you so much for helping me! and @aj1973 thank you so much for your advice and insight!
stay safe & healty!
and have an outstanding day ahead!



aj1973
Community Champion
Community Champion

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

  

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

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
Not applicable

haniya_azzahra_0-1595173687178.png

Needed ouput

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.