Forum Discussion

sbowles's avatar
sbowles
Regular Visitor
10 years ago

How do i create a date table ?

I was trying to create  a date table for filtering.. And ran across this DAX (below) ... thinking it looked useful.  However when I went to create the table and use it.. Didn't seem to work..    e...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

How do I create a column with number of working days in a year for the financial year? monday to friday. 

 

Meaning if it starts in Nov 1

That 11/01/2020 Day Column starts with day 1 and ignores saturdays and sunday in the count

 

Is this possible?

edhans's avatar
edhans
Community Champion
6 years ago

See this formula. You'll have to tweak it if you are not on a calendar year. The [Year] column would need to refer to a [Fiscal Year] for example.

 

WorksDay Count = 
VAR CurrentDay = 'Date'[Date]
VAR CurrentYear = 'Date'[Year]
VAR WorkdayCount = 
COUNTROWS(
    FILTER(
        ALL('Date'[Date],'Date'[IsWorkDay],'Date'[Year]),
        'Date'[Date] <= CurrentDay
            && 'Date'[Year] = CurrentYear
            && 'Date'[IsWorkDay] = TRUE()
    )
)
RETURN
    WorkdayCount

 

It relies on another column called IsWorkday, which is:

 

IsWorkDay = WEEKDAY('Date'[Date],2) < 6

 

 

 

With the IsWorkDay column, it is easy to count, filter, or determine if a day is a workday in visuals, measures, etc.

 

Creating a Dynamic Date Table in Power Query