cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CountingPeople
Frequent Visitor

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 new change is detected.

'Start of Month' is the date I am using to create a row for each month

'FTE Change' is showing when an FTE change was made for the employee

'FTE for the month' should now show the latest (earlier?) FTE % until a new value is provided in FTE change. so in the below example: starting with 60, until 1.0.2019 it changes to 70...

Screenshot 2022-11-11 213212.jpg

Tried the following formula but it fills the blanks only with 100 instead of the previous FTE Change, what am I doing wrong?

FTE for Month = 

VAR _LastFTEChange = CALCULATE(MAX('Beschäftigungen nach Monat'[FTE change]),FILTER('Beschäftigungen nach Monat','Beschäftigungen nach Monat'[Start of Month]<= EARLIER('Beschäftigungen nach Monat'[Start of Month])),'Beschäftigungen nach Monat'[FTE change]>0,ALL('Beschäftigungen nach Monat'))
RETURN
IF(ISBLANK('Beschäftigungen nach Monat'[FTE change]),_LastFTEChange,'Beschäftigungen nach Monat'[FTE change])

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@CountingPeople 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/3395....
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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@CountingPeople 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/3395....
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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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:

Screenshot 2022-11-22 074248.jpg

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!

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

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors