The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Please I need help.
I have a DAX measure that shows the Total Sessions Completed in my report.
I want to write a DAX to show the Average Number of Sessions for the past 6 weeks.
I need the DAX to meet the following conditions.
1. If I select any day of the week e.g Monday.
I would want the DAX measure to calculate the average number of sessions completed for the past 6 Monday.
if the selected day is a Tuesday, I want the DAX to calculate the average number of sessions completed for the past 6 Tuesdays.
so whatever DAY I select on my date filter I want to see the average number of sessions completed for the selected day.
Solved! Go to Solution.
Hi, @lamlamm24
Thanks for @PhilipTreacy reply. You can refer to the following measures.
Measures:
DateWeekDay =
VAR _date_date =
SELECTEDVALUE ( 'Date'[Date] )
VAR _weekDay =
WEEKDAY ( _date_date, 2 )
RETURN
_weekDay
TableWeekDay =
VAR _table_date =
SELECTEDVALUE ( 'Table'[Date] )
VAR _date_date =
SELECTEDVALUE ( 'Date'[Date] )
VAR _table_status =
SELECTEDVALUE ( 'Table'[status] )
VAR _weekday =
WEEKDAY ( _table_date, 2 )
VAR _isSameWeekday =
IF ( _weekday = [DateWeekDay], 1, 0 )
VAR _isInSixWeeks =
IF ( _table_date < _date_date && _table_date >= _date_date - 42, 1, 0 )
VAR _filterDate =
IF ( _table_status = "Completed" && _isSameWeekday && _isInSixWeeks, 1, 0 )
RETURN
_filterDate
Previous Six Weeks Completed Counts =
CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [TableWeekDay] = 1 ) )
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, @lamlamm24
Thanks for @PhilipTreacy reply. You can refer to the following measures.
Measures:
DateWeekDay =
VAR _date_date =
SELECTEDVALUE ( 'Date'[Date] )
VAR _weekDay =
WEEKDAY ( _date_date, 2 )
RETURN
_weekDay
TableWeekDay =
VAR _table_date =
SELECTEDVALUE ( 'Table'[Date] )
VAR _date_date =
SELECTEDVALUE ( 'Date'[Date] )
VAR _table_status =
SELECTEDVALUE ( 'Table'[status] )
VAR _weekday =
WEEKDAY ( _table_date, 2 )
VAR _isSameWeekday =
IF ( _weekday = [DateWeekDay], 1, 0 )
VAR _isInSixWeeks =
IF ( _table_date < _date_date && _table_date >= _date_date - 42, 1, 0 )
VAR _filterDate =
IF ( _table_status = "Completed" && _isSameWeekday && _isInSixWeeks, 1, 0 )
RETURN
_filterDate
Previous Six Weeks Completed Counts =
CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [TableWeekDay] = 1 ) )
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @lamlamm24
How to Get Your Question Answered Quickly - Microsoft Fabric Community
Please supply some data we can work with, files or text, not pictures.
Regards
Phil
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
94 | |
85 | |
69 | |
65 |
User | Count |
---|---|
240 | |
125 | |
122 | |
82 | |
80 |