- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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! 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 () )
))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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...)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 () ))
))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))
))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@AdaKAda thanks very much! it works
but cant figure out the measure 2. could you help, please? 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
09-06-2024 08:55 AM | |||
09-20-2024 05:44 AM | |||
Anonymous
| 06-29-2022 03:20 PM | ||
07-29-2024 08:00 AM | |||
06-24-2024 08:39 PM |
User | Count |
---|---|
128 | |
81 | |
59 | |
57 | |
44 |
User | Count |
---|---|
184 | |
111 | |
82 | |
66 | |
52 |