cancel
Showing results for
Did you mean:
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.

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)

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)

Thank you for help, much appreciated

1 ACCEPTED SOLUTION
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! 🙂

8 REPLIES 8
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 () )
))

Frequent Visitor

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

Helper I

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 () ))
))

Frequent Visitor

@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)

Helper I

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]))
))

Frequent Visitor

Frequent Visitor

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

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! 🙂

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors