cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martysk
Frequent Visitor

Count of previous week

Hello all,

 

As im beginner with PBI, im coming to experts here. I have following issue:

I have table with 2 columns (Target date and Status) in excel.

martysk_2-1675932826661.png

 

I want to create 2x DAX measures.

 

Measure 1 conditions:

  • Filter only In progress ,In review and Not started rows in Status collumn
  • Filter only rows with dates in Target date collumn (Avoid empty rows)
  • Count only rows with dates which are in range of current week (means in the range of 6 to 12th of February)

martysk_0-1675932511232.png

 

 

Measure 2 conditions:

  • Filter only In progress ,In review and Not started rows in Status collumn
  • Filter only rows with dates in Target date collumn (Avoid empty rows)
  • Count rows with dates which have dates until end of previous week (all the dates until 5 of February)

martysk_1-1675932614576.png

 

Thank you for help, much appreciated

1 ACCEPTED SOLUTION
AdaKAda
Helper I
Helper I

Try this: 
Measure1 =
CALCULATE (
COUNTROWS ( YourTableName),
FILTER (
YourTableName,
YourTableName[Status] in ({"In progress", "In Review", "Not started"})
&& WEEKNUM ( YourTableName[Target Date] )
= WEEKNUM ( TODAY () )
&& YEAR ( YourTableName[Target Date] ) = YEAR ( TODAY () )
))

Hopefully you can work out measure 2! 🙂 

View solution in original post

8 REPLIES 8
AdaKAda
Helper I
Helper I

For Measure2 you just need to substract from the week number: 

Measure2 =
CALCULATE (
COUNTROWS ( YourTableName),
FILTER (
YourTableName,
YourTableName[Status] in ({"In progress", "In Review", "Not started"})
&& WEEKNUM ( YourTableName[Target Date] )
= WEEKNUM ( TODAY () -1 )
&& YEAR ( YourTableName[Target Date] ) = YEAR ( TODAY () )
))

 

 

@AdaKAda Thanks, but second measure doesnt work 😞

It counts only the dates for 2023 year and I would like to count all the years (2022,2021,etc...)

OK so all weeks which are NOT current week? 😄

Measure2 =
CALCULATE (
COUNTROWS ( YourTableName),
FILTER (
YourTableName,
YourTableName[Status] in ({"In progress", "In Review", "Not started"})
&& NOT

(WEEKNUM ( YourTableName[Target Date] ) = WEEKNUM ( TODAY () )
&& YEAR ( YourTableName[Target Date] ) = YEAR ( TODAY () ))
))

 

 

@AdaKAda  - I want to count rows from all the weeks (from this year and previous years) before current week. But measure 2 is still giving me incorrect value (I believe it counts blank rows in target date as well, for some reason)

If the problem is blanks, this should do: 

Measure2 =
CALCULATE (
COUNTROWS ( YourTableName),
FILTER (
YourTableName,
YourTableName[Status] in ({"In progress", "In Review", "Not started"})
&& NOT

(WEEKNUM ( YourTableName[Target Date] ) = WEEKNUM ( TODAY () )
&& YEAR ( YourTableName[Target Date] ) = YEAR ( TODAY () ))

&& NOT(ISBLANK(financials[Date2]))
))

 

Thanks for help @AdaKAda . Much appreciated your support 🙂

martysk
Frequent Visitor

@AdaKAda thanks very much! it works

but cant figure out the measure 2. could you help, please? 🙂

AdaKAda
Helper I
Helper I

Try this: 
Measure1 =
CALCULATE (
COUNTROWS ( YourTableName),
FILTER (
YourTableName,
YourTableName[Status] in ({"In progress", "In Review", "Not started"})
&& WEEKNUM ( YourTableName[Target Date] )
= WEEKNUM ( TODAY () )
&& YEAR ( YourTableName[Target Date] ) = YEAR ( TODAY () )
))

Hopefully you can work out measure 2! 🙂 

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors