Forum Discussion
Static Previous weeks Counts
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 |
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
6 Replies
Hi
I think your formula is generating 30
Regards,
Sayali
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
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
)
)
