Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

I want to get the recent data out of the record with Date (with recent Time as well)

Hello,

I am trying to fetch the recent data not only to the recent date but to the recent time as well and then provide the count of that data afterwards. I am trying multiple queries provided in the forum but none has worked so far. 

kushagra2626_0-1644213583525.png

The data is something like this and the yellow rows are the recent record. Please provide me with a solution weather I should do it as a calculated columns or a measure. And if a new column has to be made, how should I do that.

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Anonymous 
Here's your sample file https://www.dropbox.com/t/njvPDqHidpQ3dSxs
You can create a calculated column that checks which rows to keep:

 

Keep? = 
VAR CurrentDate = Data[DateTimeFetched (ST 24hrs)]
VAR CurrentServer = Data[ServerName]
VAR EachServerTable = 
    FILTER (
        Data,
        Data[ServerName] = CurrentServer
    )
VAR LastDateTimeFetched =
    MAXX (
        EachServerTable,
        Data[DateTimeFetched (ST 24hrs)]
    )
RETURN
    IF (
        CurrentDate = LastDateTimeFetched,
        "Yes"
    )

 

Then create a new calculated table that keeps only the required rows:

 

Filetered Table = 
FILTER (
    Data,
    NOT ISBLANK ( Data[Keep?] )
)

 

Your measure would be just the COUNTROWS of this table
Untitled.png

Untitled.png

Untitled.png

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hello Tamerj,

Thanks for the solution you provided and your solution worked nicly for me and it did solved my issue.

However there is one scenario that I am facing with the solution. We are providiing the latest date time for all the servers whatever the last latest date it is in the data. But I want to give only the latest time with respect to the todays date. To simplyfy the problem I am attaching the screenshot of it below.

 

kushagra2626_0-1645517718042.png

Can you please help me out here. Thanks in advance.

tamerj1
Super User
Super User

Hi @Anonymous 
Here's your sample file https://www.dropbox.com/t/njvPDqHidpQ3dSxs
You can create a calculated column that checks which rows to keep:

 

Keep? = 
VAR CurrentDate = Data[DateTimeFetched (ST 24hrs)]
VAR CurrentServer = Data[ServerName]
VAR EachServerTable = 
    FILTER (
        Data,
        Data[ServerName] = CurrentServer
    )
VAR LastDateTimeFetched =
    MAXX (
        EachServerTable,
        Data[DateTimeFetched (ST 24hrs)]
    )
RETURN
    IF (
        CurrentDate = LastDateTimeFetched,
        "Yes"
    )

 

Then create a new calculated table that keeps only the required rows:

 

Filetered Table = 
FILTER (
    Data,
    NOT ISBLANK ( Data[Keep?] )
)

 

Your measure would be just the COUNTROWS of this table
Untitled.png

Untitled.png

Untitled.png

Anonymous
Not applicable

Hello Samarth,

Thanks for your time and valuabel reply. I tried the above provided solution but sadly it is not giving me the expected result.

Let me modify my question... 

 

kushagra2626_0-1644301490959.png

I want to calculate the number of servers (count) as in how many are Registered/Unregistered and Working/Not working on the "latest date and time" (in yellow) and display the count in a card. I think we need to write some measure here. I tried some measure but it didnt work for me.

As I cannot put the actual data here but this is somehow my use case. Now assume this data for some 100k record. 

Samarth_18
Community Champion
Community Champion

Hi @Anonymous ,

 

There are certain way to achive this:-

1. You can directly drag DateTimeFetched column on your visual and mark summerization as Latest.

 

Samarth_18_0-1644217197681.png

2. Create a measure with below code and use it on your visual:-

Measure = MAX('Table'[DateTimeFetched])

Samarth_18_1-1644217250131.png

 

Thanks,

Samarth

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors