Forum Discussion

PBIdeas's avatar
PBIdeas
Advocate I
5 years ago

Calculation based on Start and End dates

Hi,

 

I'm trying to build a calculation for a CTC model (Cost-to-Complete) with three conditions:

 

  1. Only calculate from a given (dynamic) start date based on when the model was updated ("Update" measure)
  2. Only calculate for days after the start date of a given resource and before its end date
  3. Only calculate specific working days (defined as the "Days CTC" and represented with a 1 in that column in the calendar table)

 

The measure I have built is as follows:

 

All resources are correctly being calculated until January 2, 2022 but then some of them no longer have values because its their end date.

 

The problem arises when I try to plot this under a time axis as the values are not shown. I suspect this is happening because, unlike the time dimension which is continuous, the formula returns values only for some days (e.g. it skips from December 16, 2021 to January 2, 2022) , but cannot get this to work. 

 

Thanks in advance for any help!

 

 

 

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    PBIdeas , At start you are using Sum(Table[Start Date]) , Try , Min for start date and Max for end date . Also make sure you calendar has all the dates till last end date 

    • PBIdeas's avatar
      PBIdeas
      Advocate I
      5 years ago

      Many thanks Amit!

       

      This has fixed the date issue, however when I now multiply the CTC days by the daily Revenue rate the values remain  the same even when people are no longer included in the CTC days - see below:

       

       

      What is wrong here? It looks like the results are not being interpreted as aggregations.