Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
martysk
Helper I
Helper I

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
Helper I
Helper I

@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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.