Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.