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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LAndes
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 

1 ACCEPTED SOLUTION


Thank you- I see the logic in your approach but I am having trouble with the synax for the "Earlier" section. Per your note, I did the following:
MaxDate = CALCULATE(MAX('Community Leadership Assessment (2)'[Date Talken ], FILTER('Community Leadership Assessment (2)','Community Leadership Assessment (2)'[Name]=EARLIER('Community Leadership Assessment (2)'[Name]))))
But i got this error-A single value for column 'Date Talken ' in table 'Community Leadership Assessment (2)' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Any thoughts?
@CheenuSing wrote:

Hi @LAndes

 

Please try the following

 

1. Create a column called MaxDate

    MaxDate =    

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

 

What this does is computes the MaxDate by Name and popluates in all records grouping by Name.

 

2. Create a column called IsLatest

    IsLatest = If([Date]=[MaxDate],"Latest","Older")

 

3. Now create a report with relevant columns from yourtablename and use IsLatest column as a Visual Level Filter filtered for "Latest".

 

Check it out.

 

If it works please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing


 

View solution in original post

28 REPLIES 28

Hi All,

This looks to be working from a data perspective. However I am getting something weird happening when I bring this into a visualisation.

You can see from my from the below that the data is working as intended with the formula's I have. For the purpose of my report I have added an additional filter so that it gives me the latest date for each candidateID on each joborderid as I can have multiple candidates working on different jobs.

 

ThomasBHudson_0-1665638819605.png

 

My issue is though, when I bring this into a table visualisation, both records appear with a value of 1 in column IsLatest and I am struggling to understand why it would be doing this?

ThomasBHudson_2-1665638998495.png

 


Any help would be appreciated.


Tom

 



alikhan_3
Frequent Visitor

We have similar case.

We need to select the most recent value based on the latest DateTime and DIA value. can someone give us the idea to resolve this?

pic2.jpgpic1.jpg

 

Hi@alikhan_3 ,

 

Can you share what is the exact ouput you require based on the table provided.

 

Can you post the data to One Drive / Google Drive and share the link here to find a solutiom.

 

 

Cheers

 

CheenuSing

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

Proud to be a Datanaut!
Anonymous
Not applicable

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

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

Jsimonson
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

robertlowens266
Frequent Visitor

Is there any way this can be done in the "Edit Query" section rather than at the visualization step? I am looking to filter this in the query editor to create a unique list of values to create a relationship between tables. So, that needs to be done at the query level as compared to the visualization level. Thanks!

 

I attempted the Calculate formula listed above and the custom column editor gave me a "The name Calculate wasnt recognized"

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

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!

Great solution.  Thanks!

Worked great! Thanks!

tringuyenminh92
Memorable Member
Memorable Member

Hi @LAndes,

Could you post your expression and data structure? So I quickly check it

Sure.. Here is an example:

 

Date Talken NameQ1.Q2Q3Q4
2/23/2016John SmithDisagreeDisagreeNeither agree or disagreeDisagree
2/22/2016John SmithAgreeAgreeAgreeAgree
9/23/2016Jones, NancyAgreeAgreeAgreeAgree
########Jones, RejaNeither agree or disagreeNeither agree or disagreeNeither agree or disagreeNeither agree or disagree
9/22/2016Anderson, ElaineNeither agree or disagreeAgreeAgreeNeither agree or disagree
9/6/2016Anderson, ElaineAgreeNeither Agree or DisagreeDisagreeNeither Agree or Disagree


See the issue?

Hi @LAndes

 

Please try the following

 

1. Create a column called MaxDate

    MaxDate =    

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

 

What this does is computes the MaxDate by Name and popluates in all records grouping by Name.

 

2. Create a column called IsLatest

    IsLatest = If([Date]=[MaxDate],"Latest","Older")

 

3. Now create a report with relevant columns from yourtablename and use IsLatest column as a Visual Level Filter filtered for "Latest".

 

Check it out.

 

If it works please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

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

Proud to be a Datanaut!

This is awesome!  Such a simple solution and works for a problem I had been thinking about for a while but wasn't a hassle until one of my users asked about it today.

 

Really apprecaite it!

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

xharx
Regular Visitor

Thanks! This worked immediatly, at first it gave me errors in Excel. Your addition helped me out.

 

Thanks again to you all!

xharx
Regular Visitor

Sorry I am e newbie at this forum, I presumed "Reply" would reply to the post is was under.

 

My previous post was meant as a reaction at @JohnH

 

For his addition:

 

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

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors