Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 :
Solved! Go to 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
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS ☺
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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
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
Hi @aj1973 ,
Thanks for the information ,
for your information when i tried to filter with slicer it give the wrong value
but when i tried to filter in power query it gives the right value
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
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?
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
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS ☺
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
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
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!
Needed ouput
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |