Show only data from the latest date

[ChristianTD]

Dear all,

I have a very basic question. I constantly struggle with showing only the data with the newest date. What is the easiest way to get a visual only to show data from the most recent date.

As I see it there are multiple ways – a measure, filter(although this does not work for me), row count in the source.

I have a simple example – data set:

Date                  |type        |value|

20-06-2018          A             20

20-06-2018          B             40

19-06-2018          A             21

19-06-2018          B             39

And my table visual should then only show:

Date                 |type         |value|

20-06-2018          A             20

20-06-2018          B             40

Many thanks

\Chr

[Anonymous]

@v-jiascu-msft  Hi is there a way to do this in edit query? so that when my data is refreshed, it is much faster..

[Anonymous]

Hi @Anonymous 

A sample PowerQuery/M (from Query Editor) to get only latest records as below. To reduce confusion between various reserved words, I have added "Col" to all column names:

let
Source = Table1,
#"Grouped Rows" = Table.Group(Source, {"DateCol"}, {{"MaxDateCol", each List.Max([DateCol]), type date}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"DateCol"}, Table1, {"DateCol"}, "Table1", JoinKind.Inner),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1",
{ "TypeCol", "ValueCol"},
{ "Table1.TypeCol", "Table1.ValueCol" }
)
in
#"Expanded Table1"

[sandip]

How to load table data by Max date by default and will changes accordingly by user's date selection

I have a table with load date and the requirement is to load data by max load date when it will load for the first time, but there is also a Filter drop down with load date and when the user will select another date , the table will load with that date.

TO achieve this I have used below measure :

Measure 2 =

var vMaxLoadDate = CALCULATE ( MAX ( EDG_COUNT_STATUS[Date] ), ALL ( 'EDG_COUNT_STATUS' ) )

return

IF(MIN(EDG_COUNT_STATUS[Date]) = vMaxLoadDate,1,0)

and set it as filter in visual filter level and set the value to it as 1. But it always load data with latest date, but when I am going to select another date from filter dropdown it does not chnage the dates in the table.

But my requirement is to load table with selected date as well as by Max load by default.

How can i achive this? Below is the screen shot for reference:

So by default it is loaded with 03/15/2020 which is max load date, but when ever I am going to select another date from filter values, it does load with that date.

[wesen]

Hi@v-jiascu-msft
Can we apply same senario for direct quirey mode. Because am a able to get such DAX like var in direct quirey mode.
Thank you

[Anonymous]

This is great, thank you much! 

[Anonymous]

And if he'd sorted desc by date and remove duplicates for ID&Date? Assuming PBI takes first occurred and drops the rest, wouldn't that be better for more ID's?