Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello all,
I have a date table something like this given below (3rd column is the measure which I want) :
Date | Day | Working Day |
01-11-2017 | Wednesday | 25 |
02-11-2017 | Thursday | 24 |
03-11-2017 | Friday | 23 |
04-11-2017 | Saturday | 22 |
05-11-2017 | Sunday | 22 |
06-11-2017 | Monday | 21 |
07-11-2017 | Tuesday | 20 |
08-11-2017 | Wednesday | 19 |
09-11-2017 | Thursday | 18 |
10-11-2017 | Friday | 17 |
11-11-2017 | Saturday | 16 |
12-11-2017 | Sunday | 16 |
13-11-2017 | Monday | 15 |
14-11-2017 | Tuesday | 14 |
15-11-2017 | Wednesday | 13 |
16-11-2017 | Thursday | 12 |
17-11-2017 | Friday | 11 |
18-11-2017 | Saturday | 10 |
19-11-2017 | Sunday | 10 |
20-11-2017 | Monday | 9 |
21-11-2017 | Tuesday | 8 |
22-11-2017 | Wednesday | 7 |
23-11-2017 | Thursday | 6 |
24-11-2017 | Friday | 5 |
25-11-2017 | Saturday | 4 |
26-11-2017 | Sunday | 4 |
27-11-2017 | Monday | 3 |
28-11-2017 | Tuesday | 2 |
29-11-2017 | Wednesday | 1 |
30-11-2017 | Thursday | 0 |
I want to calculate Remaining Working Days for that particular month (3rd column) using DAX function (Measure), but I can't get the exact value.
All I want to do is,
If the User select the 2nd November,
No. of Working days = Remaining days in selected month (28) - Sundays(4) = 24
If the User select the 10th November,
No. of Working days = Remaining days in selected month (20) - Sundays(3) = 17
If the User select the 16th November,
No. of Working days = Remaining days in selected month (14) - Sundays(2) = 12
If the User select the 22nd November,
No. of Working days = Remaining days in selected month (8) - Sundays(1) = 7
If the User select the 28th November,
No. of Working days = Remaining days in selected month (2) - Sundays(0) = 2
I can't get the same value.
Can anyone help me to solve this ?
Thanks in advance.
Solved! Go to Solution.
Try this MEASURE
Working Days = CALCULATE ( COUNTROWS ( TableName ), FILTER ( ALL ( TableName ), TableName[Date] > SELECTEDVALUE ( TableName[Date] ) && MONTH ( TableName[Date] ) = MONTH ( SELECTEDVALUE ( TableName[Date] ) ) && YEAR ( TableName[Date] ) = YEAR ( SELECTEDVALUE ( TableName[Date] ) ) && TableName[Day] <> "Sunday" ) )
Thank You for giving your golden time to give the answer.
I have found the solution.
Here is the updated Formula:
Working Days =
CALCULATE (
COUNTROWS ( DateTable ),
FILTER (
ALL ( DateTable ),
(TableName[Date]) > MAX(TableName[Date])
&& TableName[Date].[MonthNo] = MONTH(MAX(TableName[Date]))
&& TableName[Date].[Year] = YEAR(MAX(TableName[Date]))
&& TableName[DayName] <> "Sunday"
)
)
Thank You.
Try this calculated column
Working Days = CALCULATE ( COUNTROWS ( TableName ), FILTER ( ALL ( TableName ), TableName[Date] > EARLIER ( TableName[Date] ) && MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) ) && TableName[Day] <> "Sunday" ) )
Hello @Zubair_Muhammad,
Thank you for reply.
I have tried this thing, but can't get the right value.
May be I have not only one month's date, i have 3 year's date. i think that's why it is not giving me the right value.
One more thing,
from the above formula, i have to create "Calculated Column", because I think we can't use "Earlier" function in measure and also I want to use this in another measure whose formula is Total Sales / Remaining Working Days.
I want the Remaining Working Days of that month.
For example, if I select the date range of 1st November to 10th November,
then the answer should be 17 (20 days remaining - exclude sundays).
I want to create measure.
Is it possible ?
Thank You.
Try this MEASURE
Working Days = CALCULATE ( COUNTROWS ( TableName ), FILTER ( ALL ( TableName ), TableName[Date] > SELECTEDVALUE ( TableName[Date] ) && MONTH ( TableName[Date] ) = MONTH ( SELECTEDVALUE ( TableName[Date] ) ) && YEAR ( TableName[Date] ) = YEAR ( SELECTEDVALUE ( TableName[Date] ) ) && TableName[Day] <> "Sunday" ) )
Thank You for giving your golden time to give the answer.
I have found the solution.
Here is the updated Formula:
Working Days =
CALCULATE (
COUNTROWS ( DateTable ),
FILTER (
ALL ( DateTable ),
(TableName[Date]) > MAX(TableName[Date])
&& TableName[Date].[MonthNo] = MONTH(MAX(TableName[Date]))
&& TableName[Date].[Year] = YEAR(MAX(TableName[Date]))
&& TableName[DayName] <> "Sunday"
)
)
Thank You.
@Zubair_Muhammad
Thank You !!!
It's working.
but when I select the range of dates, it gives me Blank Value.
For example, If I select 1st November, it gives 25. that is perfect.
========>
But when I select 1st November to 5th November, it gives blank instead of 21.
========>
I also tried to get Maximum date from selected date, however i got the blank value.
Working Days = CALCULATE ( COUNTROWS ( TableName ), FILTER ( ALL ( TableName ), TableName[Date] > MAX ( TableName[Date] ) && MONTH ( TableName[Date] ) = MONTH ( SELECTEDVALUE ( TableName[Date] ) ) && YEAR ( TableName[Date] ) = YEAR ( SELECTEDVALUE ( TableName[Date] ) ) && TableName[Day] <> "Sunday" ) )
Please see in the above formula at Underline.
Please correct me if I put that at wrong place.
Thanks You.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |