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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi 

       

      Thanks that worked perfectly,

       

      However seems like the date table is not working connected with my data. Im trying to calculare the % change day over day. And i get wrong numbers when calculating with my date field on my data table, however when i use the date field from my data table then works but it doesnt exclude weekends as a result i get wrong values every monday of the dates.

      Norm. Price = CALCULATE(sumX(Winter_Contracts_Zema,Winter_Contracts_Zema[Price]), FILTER('Date','Date'[IsWorkDay]= TRUE())) + 'Normalized Value'[Normalized Value Value]

      Norm Price: Its my price field plus a variable [NormValue] = Value from 1 to 5 that changes the formula dynamically.

      This is the DAX I'm using to calculate Last day value, which  gives me incorrect values for all mondays as it shows (2) which is the variable im adding to my price field.

      Norm_Price_LD = CALCULATE([Norm. Price],ALLEXCEPT(Winter_Contracts_Zema,Winter_Contracts_Zema[Date]),DATEADD(Winter_Contracts_Zema[Date],-1,DAY))

       

       

      However, using this DAX doesnt work when using date from date table. Norm Priced just shows 2 in all columns which is the Normalized value. and the DayoDay % still shows 2 for all mondays.

       

      I also tried to add Is work day to my page filter but no luck

       

      ANy ideas?