Changing calculate column to measure help

[Anonymous]

I have a calculate column which is basically calculating max date per ID then saying if the date equals the latest date it gives me 0 or 1 then i filter my table to this, below are my currentl calculations;

Column = CALCULATE(MAX('Country[Date]),ALLEXCEPT('Country','Country'[ID]))

Column 2 = if('Countryl'[Date] = 'Country'[Column],1,0)

this gives me the ability to filter this table to the latest date per ID attached below;

At the moment this works fine, but i want to be able to see historic stuff based on someone selecting a date the chart recalculates to look at the latest date per id to let me know which country comes under the latest date. Below have attached some sample data as well that can give more context

https://we.tl/t-DjQaqzIQqG

[v-kelly-msft]

Hi @Anonymous ,

First create a date table as below:

Date table = VALUES('Table'[Date])

Then create an index column in query editor and a measure as below:

Measure = 
var _maxdates=CALCULATETABLE(VALUES('Table'[Date]),FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[Date]<=SELECTEDVALUE('Date table'[Date])))
var _maxdate=MAXX(_maxdates,[Date])
VAR _index=CALCULATETABLE(VALUES('Table'[Index]),FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[Date]=_maxdate))
Return
IF(MAX('Table'[Index]) in _index,1,0)

 And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft  thank you for coming back, i have 1 issue i am trying to display this in a bar chart so country is the axis and the value is a count of the how many Ids (based on latest date) fall into that country bracket. But can't seem to get this working 

[amitchandak]

@Anonymous , Not very clear.

Try a measure like

if(min('Country'[Date]), = calculate(min('Country'[Date]), ALLEXCEPT('Country','Country'[ID])),1,0)

[Anonymous]

@amitchandak thank you for coming back, i have 1 issue i am trying to display this in a bar chart so country is the axis and the value is a count of the how many Ids (based on latest date) fall into that country bracket. But can't seem to get this working.

[v-kelly-msft · Accepted solution]

Hi @Anonymous,

Sorry for the late reply.

Create a measure as below:

Measure 2 = 
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[Date] in FILTERS('Date table'[Date])&&'Table'[Country]=MAX('Table'[Country])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!