Get most recent / last value of the day for each day

[OMoore]

We are collecting team progress data every four hours.

In the dashboard we only want to show the latest progress at the time we look at it or for each day.

So in the table below for 25/10/2023 we would only want to see 'AssignedTask' = 44 and 'Unassignedtask' = 7.

That way as the data gets collected throughout the day we would always see the latest figures at any given time.

So if we had looked at the dashboard at 14:00 on 25/10/2023 we would see the values 'AssignedTask' = 46 and 'Unassignedtask' = 9.

The resulting dash would look something like this, which is not accurate as it is using the Max or Min, not the latests.

Your help would be greatly appreciated.

Thanks.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Assigned task: = 
VAR _latest =
    GROUPBY (
        Data,
        Data[Date],
        "@latest", MAXX ( CURRENTGROUP (), Data[Date Time] )
    )
RETURN
    CALCULATE (
        SUM ( Data[AssignedTask] ),
        TREATAS ( _latest, Data[Date], Data[Date Time] )
    )

Unassigned task: = 
VAR _latest =
    GROUPBY (
        Data,
        Data[Date],
        "@latest", MAXX ( CURRENTGROUP (), Data[Date Time] )
    )
RETURN
    CALCULATE (
        SUM ( Data[UnassignedTsk] ),
        TREATAS ( _latest, Data[Date], Data[Date Time] )
    )

[OMoore]

Hi, I changes it to using a calculated measure instead and it worked perfectly!

Thank you so much 🙂

[Jihwan_Kim]

Hi,

Thank you for your message.

My solution was for creating calculated measures.

But I guess you want to create calculated columns.

In my opinion, it has to approach in a different way.

I also can see that the structure of the table is different than what I have created as a sample.

If it is OK with you, please share your sample pbix file's link here, and then I can try to look into it.

Thanks.

[OMoore]

Hi thanks for the quick response.

Nearly there.

It seems to be working for the 'Assigned tasks'

Latest Assigned tasks =

VAR _latest =

    GROUPBY (

        'Last value of the day',

        'Last value of the day'[Date],

        "@latest", MAXX ( CURRENTGROUP (), 'Last value of the day'[Time] )

    )

RETURN

    CALCULATE (

        SUM ( 'Last value of the day'[AssignedTask] ),

        TREATAS ( _latest, 'Last value of the day'[Date], 'Last value of the day'[Time] )

    )

But I am getting an error when I repeat for unassigned?

Unassigned task: =

VAR _latest =

    GROUPBY (

        'Last value of the day',

        'Last value of the day'[Date],

        "@latest", MAXX ( CURRENTGROUP (), 'Last value of the day'[Time] )

    )

RETURN

    CALCULATE (

        SUM ( 'Last value of the day'[UnnassignedTask] ),

        TREATAS ( _latest, 'Last value of the day'[Date], 'Last value of the day'[Time] )

    )