Forum Discussion

Siboska's avatar
Siboska
Helper II
3 years ago

Dynamic Datediff measure

Hi, 

Having some issues creating a dynamic measure that can calculate the datediff inside each project idea. 

 

 

As you can see I have a lot of different projects with an end date column. 
My goal is to create a measure that can calculate the datediff inside each project in the order dictated by the Sort column. The highest number in the Sort column indicates that it is the earliest row and therefore it should be assigned 0. 

An important note is that it cannot be a calculated calumn because I need it in a matrix were I have different filter option and so on. 

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Siboska Try:

    Measure =
  VAR __Project = MAX('Table'[Project])
  VAR __EndDate = MAX('Table'[End Date])
  VAR __Sort = MAX('Table'[Sort])
  VAR __MaxSort = MAXX(FILTER(ALL('Table'), [Project] = __Project]), [Sort])
  VAR __BeginDate = 
    SWITCH(TRUE()
      __Sort = __MaxSort, __EndDate,
      MAXX(FILTER(ALL('Table'), [Project] = __Project && [Sort] = __Sort + 1), [End Date])
    )
  VAR __Result = ( __EndDate - __BeginDate ) * 1.
RETURN
  __Result
    • Siboska's avatar
      Siboska
      Helper II
      3 years ago

      Hi Greg,

      It will just return the following: 

       

      Do you have any suggestions?

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        Siboska Can you post sample data as text in a table as well as how you have your matrix configured? Then I can mock it up.