Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a simple table (1) that contains a Date column. Then I have a second table (2) that contains all holidays also on Date level.
Is there any chance to determine the number of working days - excluding holidays - between the Date column of the first table and today?
If so, how?
Solved! Go to Solution.
@joshua1990
You need to add a custom column with the following M Code in Table 1.
List.Count(
if
[Date] > Date.From(DateTime.LocalNow())
then
{}
else
List.Select(
List.Difference(
List.Dates(
[Date],
Duration.Days
(
Date.From(DateTime.LocalNow()) - [Date]
) + 1
,
#duration(1, 0, 0, 0)
),
Holidays[Holiday]
),
each Date.DayOfWeek(_,Day.Monday) < 6
)
)
I have attached the file below.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
I am trying to apply your solution to a simila problem I am having. I downloaded the attachment and followed your instructions, but it is not working. Can you please explain why you create the _Measures query and how it helps?
thank you,
@joshua1990
You need to add a custom column with the following M Code in Table 1.
List.Count(
if
[Date] > Date.From(DateTime.LocalNow())
then
{}
else
List.Select(
List.Difference(
List.Dates(
[Date],
Duration.Days
(
Date.From(DateTime.LocalNow()) - [Date]
) + 1
,
#duration(1, 0, 0, 0)
),
Holidays[Holiday]
),
each Date.DayOfWeek(_,Day.Monday) < 6
)
)
I have attached the file below.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks @Fowmy : Thanks a lot! But what is about the weekends?
I'm sorry that I missed to add this information:
Working days = Mo - Fr
@joshua1990
I added that condition as well also, attached the file after modification. Please check my 1st reply.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group