Need help with Dynamic Place Holder - Adddays (business days) to date

Jashanpreet_Sandhu
6 days ago
3replies
Answered

I need to create a placeholder text which will show me my base period and comparative period dates. Both of these text placeholders are driven by load date. I have created an interaction between the load date & the placeholders. Base period works as that is the same date as the load date. Comparative period is the 6 business days prior to the load date. What formula can I use here to accomplish this? Currently I am using AddDays -7 and it works for calendar days but I'd like to convert this to business days

Thanks

- VP Product Management
- Ian_Macdonald
- 5 days ago
- Reported - view
Hi

What do you mean by business days? Not including weekends? Or accounting for public holidays? (That gets v complicated as it depends on which country the user is in.)

Does your model only contain working days or all dates?

For any arbitrary 7 day period there are 5 working days, so you could just -5 on your adddays() function.

Please clarify exactly what result you are trying to achieve.

Thanks,

Ian
- Jashanpreet_Sandhu
- 5 days ago
- Reported - view
Hi

Yes business days mean not including weekends and public holidays.

Our model only contain working days. As you can see in the SNIP below there is no 05-25-2025 & 05-26-2025 data in our model so when I select 06-02-2025 as a load date I would like to see the comparative period date of 05-23-2025

If I do adddays() -5, I get the following results.

If I do adddays() -7, I get the following results.

Thank you
- VP Product Management
- Ian_Macdonald
- 5 days ago
- Reported - view
Hi

OK, so if your Model only contains working days, with no weekends or public holidays, then you can use that to determine the comparative period rather than trying to calculate it in Dynamic Text.

Create a custom measure that returns the caption of the current member, lag(6):

Then build a Discover that contains just that Measure. You can put a Date slicer on it to see the result.

Then add Dynamic Text based on that grid that retrieves the formatted cell contents of the grid:

Make sure there is an interaction between the slicer on the Present and that Dynamic Text:

And then run the Present and change the selected date. The comparative date will show as 6 working days prior to the selected date as it id driven from the dates in your model rather than some complicated date calculation in Dynamic Text:

Hope that helps.

Ian