Maximum of filter value in table

[bharathkumar]

Hi All,

I  wanted to compare the data between two different dates which I fulfilled with a regular line graph, but on the same page, the table to show the data for the maximum of selected date in the filter. I created a DAX measure to show the maximum of selected value as

Max_Selected =

VAR LatestDate = CALCULATE(MAX(Datecolumn),ALLSELECTED(Datecolumn))

RETURN

IF(MIN(Datecolumn) = LatestDate , 1 , 0)

But when I applied this in the filter pane of table I can see the data only if from and to dates are same. 

Can somebody help me in fixing this issue.

Regards,

Bharath.

[Ashish_Mathur]

Hi,

What result do you get with this measure?

=MAXX(ALLSELECTED(Datecolumn),Datecolumn)

[bharathkumar]

Hi Ashish,

Thanks for your reply.

I'm able to get the maximum date from the selected filter but the problem is the table has to show the data for maximum of the selected date in the slicer

I used 

Max_Selected =

VAR LatestDate = CALCULATE(MAX(Datecolumn),ALLSELECTED(Datecolumn))

RETURN

IF(MIN(Datecolumn = LatestDate , 1 , 0) 

and applied the visual filter pane value to be 1. But here I'm able to see the data only if 'from date' and 'To date' in filter slicer visual are same, which is incorrect.

Regards,

Bharath.

[v-frfei-msft · Accepted solution]

Hi @bharathkumar ,

We should create a date table to work on it. And add the date column from the date table to slicer.

slcer = CALENDAR(DATE(2019,08,01),DATE(2019,08,31))

Then we can create a measure as below.

Max_Selected = 
VAR LatestDate = CALCULATE(MAX('slcer'[Date]),ALLSELECTED('slcer'[Date]))
RETURN
IF(MIN('fact'[Date])= LatestDate,1,0)

 

[AnthonyTilley]

Try this 

Selected_Dates = 
Var a = "MIN"
Var b = "MAX"
Var c = blank()

var d = max('Table'[DATE])
var maxd = CALCULATE(max('Table'[DATE]),ALLSELECTED('Table'[DATE]))
var mind = CALCULATE(min('Table'[DATE]),ALLSELECTED('Table'[DATE]))

var ret = if(d=mind,a,if(d=maxd,b, c))

RETURN ret

in my example i have just stated "MIN" or "MAX" if the date in the row header is equal to the max or min date in the slicer and remove all others by making them blank 

you cna replace the three veriable A,B,C with your desired output

[bharathkumar]

Hi Anthony,

Thanks for your reply.

But this doesn't solve my requirement as my table should show the maximum date data and the table shouldn't be blank at any date.

Regards,

Bharath.

[AnthonyTilley]

@bharathkumar 

as i stated if you change the variables at the top you can achieve this, change the value you wish to show for min, max and everything inbetween 

for example change var c = Blank to var c = 0 and all the inbetween figures will equal 0