Yes agree that LAG LEAD cannot add rows. I've managed to amend the CTE suggestion to work how I need it, a long with the outer join approach. Basically I've...

At the moment we are using data from 1st September 2015, and have about 180,000 rows that are made up of 10,000 unique assets.

I'm not sure how I am supposed...

Thanks Drew, out of curiosity why do you think LAG is not best for the job? I tried the LAG version and your version and the LAG version runs much...

Thanks Jason, copy and paste error! Fixed in original post 🙂

J Livingston I'll give that a go thanks!!

Hopefully this is suitable!

Here is the table with my raw data

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

...

Sample data attached.

The first tab shows the raw data. Asset ID 1 was purchased on 13/11/2015 and sold on 16/11/2015. This is why the only rows that exist are between...

Help 🙂

Anyone?

Anyone?

Thanks for that, but I'm struggling to get it to work!

I've tried to use this SWITCH() statement..

CountMeasure:=SWITCH (TRUE (),FIRSTNONBLANK ( 'ConsolidatedFiles'[AdminSystem], TRUE () ) = "TB", CountA ( [DiligentaCode] ),FIRSTNONBLANK...

I don't know what to put in the Calculated Member though?

What would I put in the computed column? I can't put the TOTALMTD function can I?

I thought as much. Seems as though they've given this power to Power View! 🙁

Anyone? Please help 🙂

Wow, so simple! Thanks 🙂