Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Hayleysea
Resolver II
Resolver II

Previous week measure not working

Hi there,

 

I have this measure to calculate a percentage of complete actions (works perfectly)

% action complete = 

IF(ISBLANK(COUNTX(FILTER(table, table[Cond Form]=4 && table[Do not count]="Count"), table[ID])/ COUNTX(FILTER(table, table[Do not count]="Count"), table[ID])), "-", COUNTX(FILTER(table, table[Cond Form]=4 && table[Do not count]="Count"), table[ID])/ COUNTX(FILTER(table, table[Do not count]="Count"), table[ID]))
 
I now want to create a measure that does the same as above but for the week before the current one (not always 7 days apart). I put in the following into a measure
 
%previous week action complete =
VAR _PrevWeek = MAX( table[Week Rank] ) - 1
VAR __prevWeekWidget = CALCULATE([% action complete], table[Week Rank] = _PrevWeek)
RETURN __prevWeekWidget
 
yet it just returns a '-' when it shouldn't. Ultimately I will compare the two measures to show week on week changes but need this to work to get there.
 
Thank you!
 
5 REPLIES 5
amitchandak
Super User
Super User

@Hayleysea ,Try like this

%previous week action complete =
VAR _PrevWeek = MAX( table[Week Rank] ) - 1
VAR __prevWeekWidget = CALCULATE([% action complete],filter(all(table), table[Week Rank] = _PrevWeek))
RETURN __prevWeekWidget

 

Also, simplify like this.  Do not Mix text and number data type


% action complete =
divide(COUNTX(FILTER(table, table[Cond Form]=4 && table[Do not count]="Count"), table[ID]), COUNTX(FILTER(table, table[Do not count]="Count"), table[ID]))

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks @amitchandak . I've made the changes (% action complete working good) and now I seem to be getting a percentage for the %previous week action complete but it's not changing when I apply filters to it but it remains as 66% no matter what filter.

 

Any ideas?

@Hayleysea , I typically prefer not to use the same table for the time calculation. I always use Date Table. Can you move you dates, week , week rank to a different table and try

Refer this file

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks, it looks as though that doesn't make a difference. It seems that the Filter All function is stopping my visual level filter from applying (it works when I incorporate another filter all filter on the period after the week rank filter, just not when I use period as a visual level filter).

 

I'm hoping I don't have to create new measures for every period since I can't use visual level ones 😞

Hi @Hayleysea

 

Can you upload your .pbix file to onedrive business and share the link with me?I need to test the measure to find the problem.

 

Do remember to remove the confidential information before sharing.

 

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.