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...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Siboska Details matter. See if the attached PBIX below signature helps.

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

Hi Greg, 

The measure is working when I'm not filtering on a specific project type.

 

Now I filter on Type = "End of Month"

Is it expected that it can't dynamically calculate the date difference when filtering on a type column? If so, is there anything I can do so that the measure will work dynamically with the sort column?