Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
My data has only weekday, as per given formulas, my moving averages are considering weekends (Sat and Sudays) also as dates. How I skip those in my moving averages? please help.
My formulas:
5dayMovingSum =
CALCULATE(sum(Query1[Inv_Amount]),DATESINPERIOD(Query1[Inv_Date],LASTDATE(Query1[Inv_Date]),-5,day))
5dayMovingSum_Avg = CALCULATE(sum(Query1[Inv_Amount]),DATESINPERIOD(Query1[Inv_Date],LASTDATE(Query1[Inv_Date]),-5,day))/5
My Data screen shot:
Solved! Go to Solution.
You could try LASTDATE(Query1[Inv_Date]),-7,day)
and maybe use the AVERAGEX function for your moving average?
Hi @Murali,
Have you tried the solution provided by Phil_Seamark? As your data only contains weekday, it should work for 5 Day Moving Average in this scenario.
@Phil_Seamark Really brilliant solution! I was trying to add a "WeekDay" column to solve this which seems no necessary.
Regards
You could try LASTDATE(Query1[Inv_Date]),-7,day)
and maybe use the AVERAGEX function for your moving average?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |