Reply
New Contributor
Posts: 546
Registered: ‎02-17-2016

Re: Finding the most recent value

Hi @robertlowens266

 

We can do that in Power Query as well.

 

The approach is to find the MaxDate grouped by Name. And then add a column IsLatest by comparing the date and MaxDate .

derived. My test table had only 3 coulmns Date,Name and Response and 13000 rows.

 

The code is as under

 

 

let
    Source = Excel.Workbook(File.Contents("C:\PowerBICommunity\SampleData.xlsx"), null, true), 
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"ChangedType" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Name", type text}, {"Response", type text}}),

 

/* First Group by Name and Arrive at the MaxDate by Name */


    GroupbyName = Table.Group(ChangedType, {"Name"}, {{"MaxDate", each List.Max([Date]), type date}}),

 

/*  Do a selfJoin with the all records previously loaded using Name as common field */


    #"Merged Queries" = Table.NestedJoin(ChangedType,{"Name"},GroupbyName,{"Name"},"GroupedOnLetter",JoinKind.LeftOuter),

 

/* Expand the merged Table to select only the MAx Date */


    #"Expanded GroupedOnLetter" = Table.ExpandTableColumn(#"Merged Queries", "GroupedOnLetter", {"MaxDate"}, {"GroupedOnLetter.MaxDate"}),

 

/* Rename the column */


    #"Renamed Columns" = Table.RenameColumns(#"Expanded GroupedOnLetter",{{"GroupedOnLetter.MaxDate", "MaxDate"}}),

 

/* Lastly add the custom column IsLAtest by doing the comparision */


    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "IsLatest", each if [MaxDate] = [Date] then "Yes" else "No")

 

in
    #"Added Conditional Column"

 

 

I tested this on a 13000 rows in excel and it is very fast.

 

Test it out and feedback. And if it works please do give KUDOS.

 

Cheers

 

CheenuSing

 

Frequent Visitor
Posts: 3
Registered: ‎03-13-2018

Re: Finding the most recent value

Worked great! Thanks!

Member
Posts: 61
Registered: ‎08-23-2017

Re: Finding the most recent value

What if we want to calculate it dynamicaly, like if the date range is changed max date will now be recalculated.

Visitor
Posts: 1
Registered: ‎07-02-2018

Re: Finding the most recent value

Easier way found when I could not figure out how to do the other things people wrote:

 

To Display results on the report that match rules based on a date... 

Edit Query - Add column tab - Select Date - Filter date by rule

 date column.jpgdate filter.jpg

New Member
Posts: 1
Registered: ‎07-16-2018

Re: Finding the most recent value

Hi , But this will work only for latest date but i need to time travel, For example, I can select earlier dates then i need to see the on that particular date state how we can do, here the problem is it filter with latest event but when i am selecting some other dates as filter then it will now showing the event counts how we can achieve

Frequent Visitor
Posts: 5
Registered: ‎07-13-2018

Re: Finding the most recent value

@CheenuSing Is there any way to make the IsLatest work according to slicers?

As in if I have selected April, IsLatest is applied only to April and if I have selected a year the IsLatest is applied to the whole year?

New Contributor
Posts: 546
Registered: ‎02-17-2016

Re: Finding the most recent value

Hi @Vishwanathraoyl

 

Sorry for the delayed response.  Can you please put some data and the output expected on google drive or one drive and share the linke here.  This will enable me understand your requirement better and provide a soultion.

 

 

Cheers

 

CheenuSing

Highlighted
Frequent Visitor
Posts: 5
Registered: ‎07-13-2018

Re: Finding the most recent value

@CheenuSing The below code works well for my requirement:

KPI= COUNTROWS (

    FILTER (

        ADDCOLUMNS (

            CALCULATETABLE (

                SUMMARIZE (

                    'Table1',

                    'Table1'[ID],

                    Table1[SalesRepName],

                    "Rep",Table1[SalesRepName],

                    "POP", 'Table1'[ID],

                    "Latest", MAX ( 'Table1'[Date] )

                ),Table1[KPI] in {0,1}

            ),

            "Response", LOOKUPVALUE (

                'Tabke1'[KPI],

                'Table1'[ID], [POP],

                Table1[SalesRepName],[Rep],

                'Table1'[ActivationStandard],1,

                'Table1'[Date], [Latest]

            )

        ),

        [Response]

    )

)

 

I basically wanted to make it so that the "Latest" is dynamic by the date filter as I'm building a dynamic reporting on a few surveys which should ideally not require republishing. This makes it so.

Frequent Visitor
Posts: 3
Registered: ‎05-03-2016

Re: Finding the most recent value

Thanks A LOT for this.  Very helpful, and thanks for the explanation along the way!

 

 

I could not get it to work, but after a lot of looking and researching, I noticed one parameter on the FILTER was left out by accident.

 

 

 

I got it to work with adding "yourtablename" as the first paramber in the FILTER:

 CALCULATE(MAX(yourtablename[yourDatecolumn]),FILTER(yourtablename, yourtablename[Name]=EARLIER(yourtablename[Name])))

Frequent Visitor
Posts: 4
Registered: ‎03-25-2018

Re: Finding the most recent value

Help pelase, the column is not avalaible in real time dataset, i am getting stock becouse i need the last record of  x types....