Hello, I am wanting to make a dynamic variable but seem to come off short.
In my report I have them named after the months of the year (January, February etc)
By doing =ReportName() I do get the Month as expected.
Doing =ToDate("01/" + ReportName() + "/2022"; "dd/mm/yyyy") gives me the result 01/01/2022 (All good so far!)
So I made these two Variables to get the first day and last day of the month
[PromptFrom]=ToDate("01/" + ReportName() + "/2022"; "dd/mm/yyyy") (Result: 01/01/2022)
[PromptTo]=LastDayOfMonth(ToDate("01/" + ReportName() + "/2022"; "dd/mm/yyyy")) (Result 31/01/2022)
All Good So far Until this one...
[Status]=If [Leaving Date:People] < [PromptFrom] Then "Already Gone"
ElseIf [Leaving Date:People] Between([PromptFrom];[PromptTo]) Then "Leaver"
ElseIf [Join Date Merge] Between([PromptFrom];[PromptTo]) Then "Joiner"
ElseIf [Join Date Merge] <= [PromptFrom] Then "Current"
ElseIf [Join Date Merge] > [PromptFrom] Then "Future Employee"
The results show in the table, however I am unable to filter the results as it shows no values inside. If I replace ReportName() with the name "January" it works! Could someone help me as I need it to go by the ReportName() otherwise I will have to make 96 variables for the year!
thanks in Advance.