Forum Discussion

PaulVanS267's avatar
PaulVanS267
Frequent Visitor
3 years ago
Solved

Using SUMMARIZE as a variable in NETWORKDAYS expression

Good afternoon,

 

Please could someone help me with an issue I am struggling with.  I have a Calendar dimension table that contains flags ("Y") for all UK Bank Holidays.  

 

I am trying to create an expression where:

  1. I define a variable that returns the filtered column of dates that are public holidays (to achieve this I am using the SUMMARIZE function)
  2. Use this variable in the NETWORKDAYS expression as the <holiday> parameter

 

The formula (which is not working) is:

NetWorkDays Summarize variable= 
VAR colHolidays = 
SUMMARIZE(FILTER(dimCalendar,dimCalendar[flgHoliday]="Y"),dimCalendar[Date])
RETURN
NETWORKDAYS(MAX(Projects[start_date]),MAX(Projects[end_date]),1,colHolidays)

 

The reason I thought this would work is I was able get the correct results by doing this in two stages:

First by creating a table using that SUMMARIZE formula

Table = 
SUMMARIZE(FILTER(dimCalendar,dimCalendar[flgHoliday]="Y"),
dimCalendar[Date])

 

And then I referenced that table in a NETWORKDAYS expression and it worked:

NetworkDays SUMAMRIZE Table = NETWORKDAYS(MAX(Projects[start_date]),MAX(Projects[end_date]),1,'Table')

 

Here is a screenshot of the table of results:

 

Please can you help me fix my variable definition?

 

Kind regards,

Paul

 

 

 

  • PaulVanS267 Yep, that did it, here is the fix:

    NetWorkDays Summarize Variable = //Not returning the correct result
    VAR colHolidays = 
    SUMMARIZE(FILTER(ALL(dimCalendar),dimCalendar[flgHoliday]="Y"),dimCalendar[Date])
    RETURN
    NETWORKDAYS(MAX(Projects[start_date]),MAX(Projects[end_date]),1,colHolidays) 

4 Replies

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion

        PaulVanS267 Yep, that did it, here is the fix:

        NetWorkDays Summarize Variable = //Not returning the correct result
        VAR colHolidays = 
        SUMMARIZE(FILTER(ALL(dimCalendar),dimCalendar[flgHoliday]="Y"),dimCalendar[Date])
        RETURN
        NETWORKDAYS(MAX(Projects[start_date]),MAX(Projects[end_date]),1,colHolidays)