How to set different date values based on project and/or label

Questions & Answers
1

I have a TargetEndDate measure, this is one set date I use on the forecasting chart to see when the projected date will push out past the Target End Date.

Case WHEN
DateInPeriod(‘1 Nov 2023’,[Time].CurrentHierarchyMember)
THEN
“1 Nov 2023”
END

I want to use the chart for different projects so need to expand this measure so it can set the TargetEndDate based on the project + label.

Ex:
Project A with Label X the end date is Mar. 2022
Project B with label Y the end date is Jul 2022
Project C, no labels requirement, end date is Sept 2022

Depending on the selection then that target end date will display the correct one.

Screen Shot 2021-12-06 at 7.59.31 PM
Screen Shot 2021-12-06 at 7.59.31 PM2810×714 112 KB

How do I expand on the existing CASE WHEN statement?

thanks!

2

You can use a formula instead of a particular date in this formula. For example, use an additional case when then within the DateInPeriod instead of date.

CASE WHEN
DateInPeriod(
   CASE [Project].CurrentMember.Name
      WHEN "Project A" THEN
        CASE WHEN [Label].CurrentMember.Name = "X"
        THEN DateParse("1 MAR 2022") END
      WHEN "Project B" THEN
        CASE WHEN [Label].CurrentMember.Name = "Y"
        THEN DateParse("1 JUL 2022") END
      WHEN "Project C" THEN
        DateParse("1 SEP 2022")
   END,
  [Time].CurrentHierarchyMember
  )
THEN
    Format([Time].CurrentHierarchyMember.MiddleDate, 'mmm yyyy')
END

Please make sure to use a correct case sensitive name for Label and Project in the formula.

The result and formatting for this measure should be Text.

This formula will work the best with Month level in the report. We show the month of the time period, and with week selection it could show the previous week if the beginning of the month is at the end of the week.

If you wish, you can repeat a similar case in instead of
Format([Time].CurrentHierarchyMember.MiddleDate, 'mmm yyyy')

A slight modification is needed there though. Do not use DateParse, simply add the time as a string you would like to see in the report.

Daina / support@eazybi.com

1 Like