topic Re: Selecting Last Activity in DAX Commands and Tips

Selecting Last Activity

Anonymous — Sun, 04 Apr 2021 13:36:22 GMT

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!!

Re: Selecting Last Activity

amitchandak — Mon, 05 Apr 2021 04:15:22 GMT

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

Re: Selecting Last Activity

Anonymous — Mon, 05 Apr 2021 14:02:58 GMT

Sure here it is Sir. Thanks

https://mckeehomes1093-my.sharepoint.com/:u:/g/personal/femikwesi_mckeehomes1093_onmicrosoft_com/EZ18D3fSOvlAr53R1Fx108UBAAlRgkEkqICJdeCRG1lbaQ?e=kvjORp

Re: Selecting Last Activity

Anonymous — Tue, 06 Apr 2021 10:34:46 GMT

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.

Re: Selecting Last Activity

Anonymous — Tue, 06 Apr 2021 13:47:01 GMT

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.