Dynamic Date based on Working days

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:

Where

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)