cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors