Hi Bhargavi! hope you're well!
This calc have the rules that you want:
IF DATEPART('weekday',TODAY()) <=6 AND DATEPART('day',TODAY())>=8 THEN
IF DATEDIFF('month',[Revenue Year],TODAY())=0 then //CURRENT_VALUES END
IF DATEDIFF('month',[Revenue Year],TODAY())=1 then [REVN_AMT] END
hope it helps you!
Calculations around business days is always pain. Business days depend upon the holiday calendar, weekends etc.
We had a similar problem and we solved it by introducing a calendar dimension table. We loaded all the calendar dates for next 20 years with "Business Day" and "Holiday" column.
If first of the month is a public holiday then we set second of the month as "first" business day adn so on...We set the values manually as one time effort and we lookup into this table (or use join) to determine the business day for today and accordingly use it on Tableau.
Its a a bit of had work but it works like a charm!
If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Thanks for the reply sujay. Would you mind to share the logic or elaborate a little bit about what you have said please..
Once again thanks for the response
I good hope you’re well too. Thanks for the logic shall try it out and let you know if that works
Thanks for the links. Shall try those links And let you know how it goes
Once again thanks for the help
Hello, that happens because you're only evaluating for today with TODAY() Function. Since you create the calculation on the 14th of February according to your business logic it should only show your current month.
You can test that calculation by replacing TODAY() with a manual date like #01/01/2019# or #12/08/2019#
IF DATEPART('weekday',#12/08/2019#) <=6 AND DATEPART('day',#12/08/2019#)>=8 THEN
IF DATEDIFF('month',[Revenue Year],#12/08/2019#)=0 then [REVN_AMT] END
IF DATEDIFF('month',[Revenue Year],#12/08/2019#)=1 then [REVN_AMT] END
I have created max(rev_date) as a date field and has given this date as a reference date to all of my calculated fields. And it worked like a charm.