Dynamic Date based on Working days

How can I create dynamic time comparison but based on working days?

I need to display a dashboard with daily sales.

Preferably the dynamic date option is only showing the accounting dates (working days). However it is showing full calendar, including weekends and it is up to the user to select only Mondays to Fridays. But then the comparison is made Today versus Yesterday. However this should ideally be the previous working day, as on Monday it should compare to Friday, and when we had a holiday, then it should also compare to the previous working day.


I have created a user dimension with 2 values: Today and Yesterday.

I have a flag field in the Calendar table: Workday with 0 or 1 can I use this field somehow in the dynamic date setup?

Note it should be possible for Anywhere end user to select different dates in past, so just adding this field to criteria of dashboard is not an option.



  • Hi Femke


    As I understand you, it isn't an option to just exclude all dates where workday = 0 correct?

    Because then I would do the following, as it sounds similar to a case i've encountered.  

    Lets say workday is m1, and the sales is m2

    Then the basic idea is:

    if sum(d1,-1,m1) = 1 then sum(d1,-1,m2) if not workday look an extra day back

    This would mean that if yesterday was a workday, return the sales from yesterday. 

    Then you just build on top of this, because as you say there are weekend ands holidays, depending on how many days you could be closed, dictates the amount of if statements. The following would look back, to up to 4 days, and check whether any of those days was workingdays. 

    if sum(d1,-1,m1) = 1 then sum(d1,-1,m2) else if sum(d1,-2,m1) = 1 then sum(d1,-2,m2) else if sum(d1,-3,m1) = 1 then sum(d1,-3,m2) else if sum(d1,-4,m1) = 1 then sum(d1,-4,m2) else 0


    You also need to use some user dimension, or criteria though. Depending on the history you need, you could make a dynamic date option for the last month, and then make it possible for the users change the dynamic date base. 

  • Thanks Kristoffer, it's a possibility but I don't want to have to maintain multiple if statements for every measure in every chart and table i have on the dahboard. 

  • I need something like a user dimension with values:

    Today(= selected date in dynamic date criteria)

    Day Before = Max(Date) where WorkingDay=1 and Date<Today


    I know there ahs been a post from a user requesting the possibility of added a value condition to criteria, e.g.required criteria if dimension = 'specific value' then show. Something similar is needed here I think. Being able to have a criteria based on custom formula.

  • Hi Femke, 

    I just tried something else. It its not ideal, but it works at least :)

    Create a user dimension:


    Today(= selected date in dynamic date criteria)

    LastMonthToYesterday = Last month up top yesterday, but only days that where working days.

    Besides the user dimension, add the date dimension.

    That gives you one column for today, with one date. And then the LastMonthToYesterday which gives you n columns = 30  - days closed. 


    Then make a new measure, in my case:

    allcount(d1:0, 0, m1)

    And then on that measure, you create a visibility agent like this:

    value <> 1 and value <> max(all, 0, m2)

    And then make sure, that you chose to hide user column, and not the row. 

    Then at the end, you hide the Count measure all together, and you are left with two columns. Today, and the last date that was a working day

  • thanks, I'll try this and let you know If I can get it to work (note I need it for 4 KPI components and 2 pivot tables and 1 bar chart)

  • Super, looking forward to hear if it works. 

  • Hi Kristoffer, I have it working.

    I did adapt the logic a bit. I have an user dimension but with only 1 option of Month to Date with a criteria on Workday=1

    Next I set the visibility agent to: value <> (max(all, 0, m2)-1) and value <> max(all, 0, m2).

    I can adapt this visibility to show the last 5 working days as well.

    Thanks for your solution.

  • Hi Femke, 

    Glad to hear that it worked :) 

  • 1 addition, i had to add 1 extra month in the user dimension, in case of month change. (so previous month to date till today)


Please sign in to leave a comment.

Didn't find what you were looking for?

New post