Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Populate most recent value until changed

Hi community   I have a calculated table that summarizes employees, months, and their FTE % change. Now I want to create a calculated column that populates the FTE % throughout all months until a n...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous Try:

     

    Column = 
  VAR __Value = 'Beschäftigungen nach Monat'[FTE change]
  VAR __Date = 'Beschäftigungen nach Monat'[Start of Month]
  VAR __PreviousDate = MAXX(FILTER('Beschäftigungen nach Monat', [FTE change] <> BLANK() && [Start of Month] < __Date),[Start of Month])
  VAR __PreviousValue = MAXX(FILTER('Beschäftigungen nach Monat',[Start of Month] = __PreviousDate,[FTE change])
  VAR __Return = IF('Beschäftigungen nach Monat'[FTE change] = BLANK(),__PreviousValue,__Value)
RETURN
  __Return

     

    See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
    The basic pattern is:
    Column = 
      VAR __Current = [Value]
      VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

      VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
    RETURN
      __Current - __Previous

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Anonymous Try:

 

Column = 
  VAR __Value = 'Beschäftigungen nach Monat'[FTE change]
  VAR __Date = 'Beschäftigungen nach Monat'[Start of Month]
  VAR __PreviousDate = MAXX(FILTER('Beschäftigungen nach Monat', [FTE change] <> BLANK() && [Start of Month] < __Date),[Start of Month])
  VAR __PreviousValue = MAXX(FILTER('Beschäftigungen nach Monat',[Start of Month] = __PreviousDate,[FTE change])
  VAR __Return = IF('Beschäftigungen nach Monat'[FTE change] = BLANK(),__PreviousValue,__Value)
RETURN
  __Return

 

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Greg_Deckler Thanks so much for the help, I think I got the concept!

The calculation still provides some funny results. After some investigations I found that the _previousDate does not provide consistent results:

it works for the first few rows, but then somehow fetches the wrong date. It should be 01.01.2018 until the next change.
Does sorting the table have an impact on the way the calculation works?

 

Thanks again for the help!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Greg_Deckler or anyone, can anyone help and tell me what's going wrong? help is much appreciated!