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
po
Post Prodigy
Post Prodigy

Average of previous week total blank

Hi,

 

Have an issue with following.

 

Can calculate at row level, previous weekly compliance figure for a store as shown below.

The weekly compliance figure in bold below is an average figure 

 

CALCULATE(AVERAGEX('Sales','Sales'[Weekly Compliance]) 

previous_week_total_blank.JPG

Previous Weekly compliance defined as below

CALCULATE(AVERAGEX('Sales','Sales'[Weekly Compliance]) ,filter(all
(calendar),calendar[Week End Date]=maxX(calendar,calendar[Week End Date])-7) )
 
Both mesaures abiove defined as percentage but the previous weekly compliance giving blank at total level.
 
Then will compare average weekly compliance figure to previous week average weeklyu compliance figure at level of all stores to then show in card along with arrow as to whther increased or decreased.
 
How can we get an average for the previous weekly total figure to show correctly?
 
 
 
Thanks
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Very tough without sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

If I am reading this correctly, you are looking to calculate your AVERAGEX calculation where your [Week End  Date] = the MAX [Week End Date] - 7 days. Knowing that the Total line calculates in the context of ALL, you would therefore be returning your maximum date from that table and the logic would be that you don't have a [Week End Date] that is exactly 7 days less than that.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

Very tough without sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

If I am reading this correctly, you are looking to calculate your AVERAGEX calculation where your [Week End  Date] = the MAX [Week End Date] - 7 days. Knowing that the Total line calculates in the context of ALL, you would therefore be returning your maximum date from that table and the logic would be that you don't have a [Week End Date] that is exactly 7 days less than that.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for reply and explanation.

 

Have changed to instead get the value for max date -7 and this will be the constant value which is then compared to the dynamic value of selection and working as expected now.

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.