Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calendar Table for Forecasting between Start & End Dates

I'm having difficulties with a table I am trying to create.  I need to show FTE by Date for each Project Name, Location and Grade.

 

The data I have shows FTE broken down into each location and grade i.e. UK-Director, UK-Associate, US-Director, US-Senior Associate etc, each of these are columns and have a number for how many are required in the cells.  I have taken this data and unpivoted, then I have created a couple of calculated columns so I have Location, Grade, FTE.  

 

I created a Calendar table using the Start and End Dates from the data above so min from start and max from end date.  

 

When I create a Matrix table and add in the calendar date as columns, and the project name as rows (with locations and grades below those as well), and add in FTE, then create a relationship between calendar date and start date and end date, I can only see the values for start date. 

 

What I need to be able to see is all dates from the calendar table including those between the start and end date, for exampple if I have FTE as 10, and the start date is June 14th and end date is June 16th, I should have 10 in columns June 14th, June 15th and June 16th.  

 

I took away the relationship in the table and created a measure for the FTE value per below:

FTE Allocation =
VAR CurrentDate = SELECTEDVALUE(Calendar[Date])
RETURN
CALCULATE(
    SUMX(
        'RM',
        IF(
            'Table'[Start Date] <= CurrentDate && 'Table'[End Date] >= CurrentDate,
            'Table'[FTE],
            0
        )
    )
)
 
Now I can see all dates with the correct FTE, however I need to be able to filter this data as well.  I added a slicer using the Calendar Date, and it does filter the data, so if I click 2026, everything for 2025 says 0, but I need the columns to actually disappear, so instead of showing me all the dates in 2025 as 0, remove them so that the first column I see is 1st Jan 2026.

Can anyone help me with the best way to do this?
 
Many thanks
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    Remove the 0 from the else part of the if statement, so that it will return blank instead

    FTE Allocation =
VAR CurrentDate =
    SELECTEDVALUE ( Calendar[Date] )
RETURN
    CALCULATE (
        SUMX (
            'RM',
            IF (
                'Table'[Start Date] <= CurrentDate
                    && 'Table'[End Date] >= CurrentDate,
                'Table'[FTE]
            )
        )
    )

    By returning blank instead of 0 those values should be excluded from the visual.

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    Remove the 0 from the else part of the if statement, so that it will return blank instead

    FTE Allocation =
VAR CurrentDate =
    SELECTEDVALUE ( Calendar[Date] )
RETURN
    CALCULATE (
        SUMX (
            'RM',
            IF (
                'Table'[Start Date] <= CurrentDate
                    && 'Table'[End Date] >= CurrentDate,
                'Table'[FTE]
            )
        )
    )

    By returning blank instead of 0 those values should be excluded from the visual.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Oh my word! Google, AI, you name it, I was looking it up. So simple! 
      Thank you so much!