cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate III
Advocate III

Finding the most recent value

Hi all,


I have survey data with multiple people taking the survey mutiple times.  Each row is a survey response with a date taken. The only way I know which is the "pre survey" and which is the post survey is by the date taken.  I would like to create a "prepost Colunm that read the date and designated pre or post. I have tried a combination of If and Max but I can't seem to get it to work.. Any tips?

Thanks 

26 REPLIES 26

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Worked great! Thanks!

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

Regular Visitor

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

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

@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?

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Frequent Visitor

@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.

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

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors