Selecting Last Activity

[Anonymous]

Hello All,

Pleaseeee!!! I need help from the gurus in Power BI community.

I have a 2 Fact tables I need to be able to select the last activity for the corresponding date from the table below.

I have tried to use Max date but it's not accurate. Attched is a screen shot of the table and the last activity I would like to get i 8-Final Mech but it's not the last date or max date

I have tried to use these formulas

CURRENT =

VAR LASTSTAGE = CALCULATE(

MAX('CONSTRUCTION SCHEDULE'[Index]),

ALLEXCEPT('CONSTRUCTION SCHEDULE','CONSTRUCTION SCHEDULE'[HOUSE NUMBER])

)

RETURN CALCULATE(

SELECTEDVALUE('CONSTRUCTION SCHEDULE'[STAGES]),

ALL('CONSTRUCTION SCHEDULE'),

'CONSTRUCTION SCHEDULE'[Index]=LASTSTAGE

)

and 

LAST ACTIVITY =

VAR SELECTEDDATE=MAX('CONSTRUCTION SCHEDULE'[FINISH DATE])

VAR CURRENTSTATUS =MAX('CONSTRUCTION SCHEDULE'[CURRENT STAGE])

RETURN

IF(CURRENTSTATUS == BLANK() || CURRENTSTATUS = "",

VAR LATESTSTATUSDATE =

CALCULATE(

MAX('CONSTRUCTION SCHEDULE'[FINISH DATE]),

'CONSTRUCTION SCHEDULE'[FINISH DATE]<SELECTEDDATE,

'CONSTRUCTION SCHEDULE'[CURRENT STAGE]<> BLANK()

)

RETURN

CALCULATE(

MAX('CONSTRUCTION SCHEDULE'[STAGES]),

'CONSTRUCTION SCHEDULE'[FINISH DATE]=CURRENTSTATUS

),

CURRENTSTATUS

)

Thanks!!

[Anonymous]

Hello @Anonymous 
Change your Current Stage to text format and update the dax.

LAST ACTIVITY =

VAR SELECTEDDATE=MAX('CONSTRUCTION SCHEDULE'[FINISH DATE])

VAR CURRENTSTATUS =MAX('CONSTRUCTION SCHEDULE'[CURRENT STAGE])

RETURN

IF(CURRENTSTATUS == BLANK() || CURRENTSTATUS = "",

VAR LATESTSTATUSDATE =

CALCULATE(

MAX('CONSTRUCTION SCHEDULE'[FINISH DATE]),

'CONSTRUCTION SCHEDULE'[FINISH DATE]<SELECTEDDATE,

'CONSTRUCTION SCHEDULE'[CURRENT STAGE]<> BLANK()

)

RETURN

CALCULATE(

MAX('CONSTRUCTION SCHEDULE'[STAGES]),

'CONSTRUCTION SCHEDULE'[FINISH DATE]= VALUE( CURRENTSTATUS )

),

CURRENTSTATUS

)

If it doesn't work as expected then please share the expected output.

[Anonymous]

Thanks for your reply TarunSharma.

I tried it but it didn't work.

What I am trying to achieve is to be able to show the Total WIP, Bank Loan and current Stage for each ID at every point in time.

Please see item marked in red in the below table.

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Sure here it is Sir. Thanks

https://mckeehomes1093-my.sharepoint.com/:u:/g/personal/femikwesi_mckeehomes1093_onmicrosoft_com/EZ1...