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] )
    ))
Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi, Anonymous 

Please correct me if wrongly understood what you need.

I assume that you have the custom-date-table.

By using this custom-date-table, no connection is needed at this moment, and please try to write the below.

 

SUM FTE =
CALCULATE (
SUM ( Data[FTE] ),
FILTER ( VALUES ( Data[Validfrom] ), Data[Validfrom] <= MAX ( dates[Date] ) ),
FILTER ( VALUES ( Data[Validuntil] ), Data[Validuntil] >= MIN ( dates[Date] ) )
)
 
By using the sample data that you provided, I come up with the below table visualization.
please let me know if I misunderstood.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!