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
ronaldbalza2023
Continued Contributor
Continued Contributor

12 week average

I have a visual that displays a table with a "Week Ending Tuesday" column as a date and a DAX measure for "Total Accounts Receivable". I need to create a new DAX measure that calculates the 12-week average of the "Total Accounts Receivable" measure. Consider the date filter of financial year that starts in July. Thanks in advance 🙂

1 REPLY 1
amitchandak
Super User
Super User

@ronaldbalza2023 ,

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

 

Last 12  weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-12  && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

For tuesday week

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...
https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e...

 

 

But in case you need select a date and then show 12 week trend

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

 

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Week Rank])
var _min = _max -1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Week Rank] >=_min && 'Date'[Week Rank] <=_max))

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.