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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.