Latest Value

[Anonymous]

Hi Guys,

I need some help on this. Here's my data:

Date	Store	Product	Answer
16/07/2018	McDonald	Pizza	Yes
17/07/2018	McDonald	Pizza	No
16/07/2018	McDonald	Burger	No
17/07/2018	McDonald	Burger	No
16/07/2018	KFC	Pizza	Yes
17/07/2018	KFC	Pizza	Yes
16/07/2018	KFC	Burger	No
17/07/2018	KFC	Burger	Yes

I need to always get the Answer for latest Date. Here what result should be:

Date	Store	Product	Answer
17/07/2018	McDonald	Pizza	No
17/07/2018	McDonald	Burger	No
17/07/2018	KFC	Pizza	Yes
17/07/2018	KFC	Burger	Yes

1. Can you please tell me the DAX formula to create above table?

2. Can you please tell me what should be the Measure for using Answer in a visualisation?

Thanks in advance.

Rob

[Ashish_Mathur · Accepted solution]

Hi,

You may download my file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my file from here.

Hope this helps.

[Anonymous]

Hi Ashish,

Thanks for your help.

However, I need a measure that will display the value for Answer in any visualisation. Is that possible?

And/or create a new table in DAX that filters the below data only:

Date	Store	Product	Answer
17/07/2018	McDonald	Pizza	No
17/07/2018	McDonald	Burger	No
17/07/2018	KFC	Pizza	Yes
17/07/2018	KFC	Burger	Yes

Thanks

[Ashish_Mathur]

Hi,

That is what my solution does.  Perhaps i do not understand your requirement.

[Anonymous]

Hi Ashish,

Can you please let me know the syntax for producing the below new table in DAX? 

Using this feature: 

Date	Store	Product	Answer
17/07/2018	McDonald	Pizza	No
17/07/2018	McDonald	Burger	No
17/07/2018	KFC	Pizza	Yes
17/07/2018	KFC	Burger	Yes

Thank you

[Zubair_Muhammad · Accepted solution]

@Anonymous

As a NEW Table ("Calculated Table"), you can use

New Table =
VAR temp =
    ADDCOLUMNS (
        Table1,
        "RANK", RANKX ( FILTER ( Table1, [Store] = EARLIER ( [Store] ) ), [Date],, DESC, DENSE )
    )
RETURN
    FILTER ( temp, [RANK] = 1 )