Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Measure 2 conditions:
Thank you for help, much appreciated
Solved! Go to Solution.
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! 🙂
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]))
))
@AdaKAda thanks very much! it works
but cant figure out the measure 2. could you help, please? 🙂
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! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |