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.
I have created a DAX for 7 day rolling average as -
Solved! Go to Solution.
Hi @monikamishra ,
Would you please add a weekday column in date table:
WEEKDAY = WEEKDAY(Dim_Date[Date],2)
Then try to use the following measure:
OR Cases 7 Day Rolling Average =
VAR A =
CALCULATE (
MAX ( 'Dim_Date'[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Date] <= MAX ( Dim_Date[Date] )
&& Dim_Date[WEEKDAY] > 6
)
)
VAR NUM =
MAX ( Dim_Date[Date] ) - A + 1
VAR TOTALVALUE =
CALCULATE (
[OR Cases (Total)],
FILTER (
ALL ( Dim_Date ),
Dim_Date[Date] >= A
&& Dim_Date[Date] <= MAX ( Dim_Date[Date] )
)
)
RETURN
TOTALVALUE / NUM
You can also refer to the sample pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ef5zxVgk6VBOqAeN5s...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @monikamishra ,
Would you please add a weekday column in date table:
WEEKDAY = WEEKDAY(Dim_Date[Date],2)
Then try to use the following measure:
OR Cases 7 Day Rolling Average =
VAR A =
CALCULATE (
MAX ( 'Dim_Date'[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Date] <= MAX ( Dim_Date[Date] )
&& Dim_Date[WEEKDAY] > 6
)
)
VAR NUM =
MAX ( Dim_Date[Date] ) - A + 1
VAR TOTALVALUE =
CALCULATE (
[OR Cases (Total)],
FILTER (
ALL ( Dim_Date ),
Dim_Date[Date] >= A
&& Dim_Date[Date] <= MAX ( Dim_Date[Date] )
)
)
RETURN
TOTALVALUE / NUM
You can also refer to the sample pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ef5zxVgk6VBOqAeN5s...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Do you always want Sun-Sat? Then you'll need to use WeekNumber. Add WeekNumber to your DimDate table if you don't have it in there already.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@monikamishra , Think you need last Week , please check my blog
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
or try like example
//Last seven till week end
last week =
Week Start date = maxx(allselected('Date'),'Date'[Date])+-7
Week End date = maxx(allselected('Date'),'Date'[Date])+-+ 7-1*WEEKDAY('Date'[Date],2) //sunday is week end
return
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Date]>=_min && 'Date'[Date]<=_max))
//use WEEKDAY('Date'[Date],1) for saturday
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |