Selecting most recent data in a dataset

[Tomhayw]

Hi there,

I currently have a dataset that updates regularly with an updated version of our CRM system as shown below:

I have set up this measure:

Pipeline state (snapshot) = calculate(sum('Pipeline (Snapshots)'[Count of pipeline projects]), DateDim[Date] = max('Pipeline (Snapshots)'[Date Obersrved]))

This measure takes the most recent date and visualises it.

However, as you can see the 'Monitoring' group was present on 19/08, but was not present in 22/08, but is still visualised by the measure:

Is it possible to modify my measure that would filter by the groups available on the newest date available?

Thanks in advance,

Tom

[rsbin]

@Tomhayw ,

Please give this a try:

Create a Measure:

MaxDate = MAX( 'Pipeline(Snapshots)'[Date Obersrved] )

This should evaluate to:  22 August 2022.

Then use this Measure in your measure above:

Pipeline state (snapshot) = calculate(sum('Pipeline (Snapshots)'[Count of pipeline projects]), DateDim[Date] = [MaxDate] )

This should then only capture the States that occur on 22 August.

Regards,

[Tomhayw · Accepted solution]

Hi there, that's what I did.

I found a solution to my problem:

MonthEnd = IF(CALCULATE(MAX('Pipeline (Snapshots)'[Date Observed]),FILTER('Pipeline (Snapshots)',MONTH('Pipeline (Snapshots)'[Date Observed])=MONTH(EARLIER('Pipeline (Snapshots)'[Date Observed]))))='Pipeline (Snapshots)'[Date Observed],1,0)

I added this as a calculate column - it marks each entry as a 1 or 0. A 1 indicates its the latest or the last entry in each month, hence I can use this as a filter!