Reply
martysk
Frequent Visitor
Partially syndicated - Outbound

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

Syndicated - Outbound

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

Syndicated - Outbound

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

 

 

Syndicated - Outbound

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

Syndicated - Outbound

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

 

 

Syndicated - Outbound

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

Syndicated - Outbound

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

 

Syndicated - Outbound

Thanks for help @AdaKAda . Much appreciated your support 🙂

martysk
Frequent Visitor

Syndicated - Outbound

@AdaKAda thanks very much! it works

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

AdaKAda
Helper I
Helper I

Syndicated - Outbound

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

avatar user

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!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)