Previous week measure not working

[Hayleysea]

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!

[amitchandak]

@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]))

[Hayleysea]

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?

[amitchandak]

@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

[Hayleysea]

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 😞

[v-kelly-msft]

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!