Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Monthly output without reference date

I want to create a matrix visual or line chart where I can show the development of the FTE (or something else) on a monthly base. Problem is that my dataset doesn't have a reference date column, but ...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Sorry, I forgot to consider the null in the ValidUntil column.

     

    please check the below amended measure.

     

    SUM FTE =

    CALCULATE (
    CALCULATE(SUM( Data[FTE]), FILTER(Data, Data[Validuntil] <> BLANK())),
    FILTER(Data, Data[Validfrom] <= MAX ( dates[Date] ) && Data[Validuntil] >= MIN(dates[Date])
    )) + CALCULATE( CALCULATE(SUM( Data[FTE]), FILTER(Data, Data[Validuntil] = BLANK())),
    FILTER(Data, Data[Validfrom] <= MAX ( dates[Date] )
    ))
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Anonymous 

To achieve your required output, you must create new table with calendar and record FTE value for each date.

 

With current records it isnot possible get sum for each month. For example, the FTE value is 1 for Jan 2019 to June 2019, but we cannot identify whether it means 1 FTE for each month or 1 FTE for whole 6 months. If it is the latter, there is no criteria to distinguish the 1 FTE to 6 month, proportional or evenly?

 


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.