topic Re: Get most recent / last value of the day for each day in DAX Commands and Tips

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

Anonymous — Thu, 26 Oct 2023 02:01:44 GMT

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.

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

Jihwan_Kim — Thu, 26 Oct 2023 02:54:33 GMT

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] ) )

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

Anonymous — Thu, 26 Oct 2023 03:29:01 GMT

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] )

)

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

Jihwan_Kim — Thu, 26 Oct 2023 05:09:07 GMT

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.

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

Anonymous — Thu, 26 Oct 2023 10:23:51 GMT

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

Thank you so much 🙂