Static Previous weeks Counts

[Anonymous]

I have sample data with item number and closing date in it. I am trying to display previous week (week start from Sunday) item counts always. I try to use filter function comparing current week-1 option but not getting right result. Please help us how do I achieve this. the filter condition I am using calculate (countdistinct [ItemNumber], Filter (table, weeknum([CloseDate],2)=weeknum(Today(),2)-1)). Below the sample data. Ideally I wanted to show previous week (04/18 04/24)to Item Number count (should be 30)

ITEM Number	Close Date
10658222	4/29/2021
10662565	4/28/2021
10661989	4/28/2021
10662248	4/28/2021
10660689	4/28/2021
10659459	4/28/2021
10667039	4/28/2021
10666968	4/28/2021
10666633	4/28/2021
10637215	4/27/2021
10643199	4/27/2021
10653992	4/27/2021
10634745	4/27/2021
10643143	4/26/2021
10651769	4/26/2021
10662419	4/26/2021
10639198	4/26/2021
10658973	4/26/2021
10647233	4/26/2021
10645140	4/26/2021
10636981	4/26/2021
10658152	4/23/2021
10656339	4/23/2021
10629974	4/23/2021
10626626	4/23/2021
10641859	4/22/2021
10626016	4/22/2021
10654586	4/22/2021
10660718	4/22/2021
10601366	4/22/2021
10564423	4/22/2021
10635535	4/22/2021
10653240	4/22/2021
10653464	4/21/2021
10642763	4/21/2021
10640149	4/21/2021
10645327	4/21/2021
10626634	4/21/2021
10661107	4/21/2021
10653984	4/21/2021
10659273	4/21/2021
10660462	4/21/2021
10658205	4/21/2021
10654322	4/21/2021
10658773	4/20/2021
10651493	4/20/2021
10649580	4/20/2021
10656405	4/20/2021
10657684	4/19/2021
10658030	4/19/2021
10658787	4/19/2021
10657200	4/16/2021
10648212	4/16/2021
10641595	4/15/2021
10622805	4/15/2021
10641700	4/14/2021
10543414	4/14/2021
10653786	4/14/2021
10654180	4/14/2021
10653651	4/13/2021
10614225	4/13/2021
10653079	4/13/2021
10641318	4/13/2021
10646080	4/13/2021
10643367	4/12/2021
10650454	4/12/2021
10652338	4/12/2021
10652311	4/12/2021
10624187	4/12/2021
10646517	4/12/2021
10624234	4/12/2021
10635540	4/12/2021
10651562	4/12/2021
10639460	4/12/2021
10650680	4/9/2021
10650687	4/9/2021
10650684	4/9/2021
10649020	4/9/2021
10620255	4/9/2021
10646454	4/9/2021
10647288	4/9/2021
10648150	4/9/2021
10648086	4/9/2021
10650272	4/9/2021
10650219	4/9/2021
10650194	4/9/2021
10650172	4/9/2021
10650151	4/9/2021
10649339	4/8/2021
10640924	4/8/2021
10614551	4/8/2021
10649315	4/8/2021
10645205	4/8/2021
10646839	4/7/2021
10642957	4/7/2021
10623728	4/7/2021
10645741	4/7/2021
10626603	4/6/2021
10642723	4/6/2021
10642708	4/6/2021
10641849	4/6/2021
10641809	4/6/2021
10645474	4/6/2021
10645404	4/6/2021
10604476	4/5/2021
10614559	4/5/2021
10558786	4/2/2021
10642861	4/2/2021
10622803	4/2/2021
10622799	4/2/2021
10622796	4/2/2021
10641231	4/2/2021
10622737	4/2/2021
10642980	4/2/2021
10644317	4/2/2021
10644238	4/2/2021
10643923	4/1/2021
10633006	4/1/2021
10602949	4/1/2021
10583315	4/1/2021
10630966	4/1/2021
10617222	4/1/2021
10643427	4/1/2021
10643065	4/1/2021
10609251	4/1/2021
10605656	4/1/2021
10597163	4/1/2021
10643023	4/1/2021

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please try the below.

Item count previous week =

CALCULATE (

DISTINCTCOUNT ( [ITEM Number] ),

FILTER (

'Table',

WEEKNUM ( 'Table'[Close Date], 1 )

= WEEKNUM ( TODAY (), 1 ) - 1

)

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Thanks Kim, can we add additional filter conditions this tis like current year because in my data set I have five years of data so the calculation shared accumulating all the values relavence to that week from all the months but we are expecting latest year only. 

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your message.

Please try something like the below, that one more condition is added.

Item count previous week =
VAR currentyear =
YEAR ( TODAY () )
RETURN
CALCULATE (
DISTINCTCOUNT ( [ITEM Number] ),
FILTER (
'Table',
WEEKNUM ( 'Table'[Close Date], 1 )
= WEEKNUM ( TODAY (), 1 ) - 1
&& YEAR ( 'Table'[Close Date] ) = currentyear
)
)

[Anonymous]

It is working now, thanks for the help. 

[Anonymous]

I did tied that but it was retruning below error 

A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[sayaliredij]

Hi 

I think your formula is generating 30

Regards,

Sayali