Latest 4 weeks calculations with conditions based on a unique key

[Anonymous]

Dear all, 

I have a question that will be explained using the data sample below.

What I want to do is to get the sum of 'not calculated' with some conditions: the sum of 'not calculated' for each unique key have to be more than 2 for the last 4 weeks.

note: 

There's also a slicer for date in the Power BI, the result of the calculations will be used for:

- presented directly on cards (which the latest 4 weeks need to be adjusted with the selected dates on slicer)

- used for calculations of % as numerator

Unique key	Date	Not calculated
A	2/9/2021	1
B	2/9/2021	1
C	2/9/2021	1
D	2/9/2021	0
A	2/16/2021	1
B	2/16/2021	1
C	2/16/2021	0
A	2/22/2021	1
B	2/22/2021	0
A	3/2/2021	1

Does anyone know any solution or approach regarding this?

Hope anyone can help me for this issue. Thank you!

[amitchandak · Accepted solution]

@Anonymous , With help from week blog create a date table with week rank

Now try

Last 4  weeks = CALCULATE(divide(countx(filter(summarize(Table, Table[Unique key] , "_1", countx(filter(Table, Table[Not calculated] =1),[Unique key])),[_1]>2),[Unique key]), count(Table[Unique key]))

, FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

or use distinctcount

divide(countx(filter(summarize(Table, Table[Unique key] , "_1", countx(filter(Table, Table[Not calculated] =1),[Unique key])),[_1]>2),[Unique key]), distinctcount(Table[Unique key]))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

Hi, thank you for your answer, but I got different result from the expected. Here I have follow up questions regarding your solution if you don't mind.

1. I would like to sum the resulted countx on the summarize table. But, after I replace the first countx with sumx, I got error because of the "string data type" said PBI. 

2. I have tried to follow your web for the Week Rank, but it resulted in "1" for every rows. Therefore, I tried to use other alternative which is this dax, what do you think? 

FILTER(ALL('Calendar'), 'Calendar'[Date]>=SELECTEDVALUE('Calendar'[Date lookup])-25 && 'Calendar'[Date]<=SELECTEDVALUE('Calendar'[Date lookup])))

I used the dax above since the date are up to end of May, and the visualizations need to be adjusted based on the date chosen to give result for the last 4 weeks.

Once again, thank you and hope you can have further opiniion for this. 

[Anonymous]

@amitchandak