Calculate number of tasks from the previous week (ignoring filters)

[pal95]

Hi,

In my report, I have Inspectors who are conducting inspections. I calculate the number of inspections made by the Inspectors just by using COUNTROWS and week filters and it is working great, but I would like to add the comparison of the number of inspections from this week and the previous week.

So basically, if I have week 29 now and one of my inspectors made 15 inspections this week, I would like to make a KPI comparison between his week 29 score and week 28, when he did 13 inspections.

Any ideas on how to do this? Screens attached.


Regards,

[v-jayw-msft · Accepted solution]

Hi @pal95 ,

You will need to create two tables, one for week of year and one for inspector name. Make sure there's no relationship between fact table and these two table then use these tables as slicers.

Create measures as below.

current_week = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[weeknum]=SELECTEDVALUE('weeknum'[weeknum])&&'Table'[name]=SELECTEDVALUE('name'[name])))

previous_week = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[weeknum]=SELECTEDVALUE('weeknum'[weeknum])-1&&'Table'[name]=SELECTEDVALUE('name'[name])))

Sample data for your reference.

Best Regards,

Jay

[v-jayw-msft · Accepted solution]

Hi @pal95 ,

You will need to create two tables, one for week of year and one for inspector name. Make sure there's no relationship between fact table and these two table then use these tables as slicers.

Create measures as below.

current_week = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[weeknum]=SELECTEDVALUE('weeknum'[weeknum])&&'Table'[name]=SELECTEDVALUE('name'[name])))

previous_week = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[weeknum]=SELECTEDVALUE('weeknum'[weeknum])-1&&'Table'[name]=SELECTEDVALUE('name'[name])))

Sample data for your reference.

Best Regards,

Jay

[pal95]

It works, but only to this particular indicator. How to make other visuals on the same to 'listen' to the filters?

[pal95 · Accepted solution]

I modify this a bit and it works

[amitchandak]

@pal95 , see if my blog can help

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...