DAX - Display in a chart any expression only for the Max Date

[alexanderg]

How can I show a sales or any expression in a chart only for the maximum date. For example I have the following dataset:

I created the following calculation in DAX:

SalesMaxday =
CALCULATE (
    SUM ( Tabla1[SaleAmount] ),
    FILTER ( Tabla1, Tabla1[Date] = MAX ( Tabla1[Date] ) )
)

This is the result I get:

So... As it is seen, as an individual calculation shows me the desirable result. But in the table chart, It shows not only the value of the maximum date, but also the rest of dates. How can I do to not show those other values, applying the filter internally in the DAX formula, because I wanna see in the table chart only the result for the max date.

Any Reply would be helpful

Regards,

[v-yulgu-msft]

Hi alexanderg,

To display the max date record only, please modify your measure expression like :

SalesMaxday =

CALCULATE ( SUM ( Tabla1[SaleAmount] ), FILTER ( Tabla1, Tabla1[Date] = Maxx(ALL (Table1[Date]), Table1[Date])

If you have any question, please feel free to ask.

Best regards,
Yuliana Gu

[alexanderg]

Hi @v-yulgu-msft Your Measure works fine but only for the maximun day in the complete dataset. It need to be based dinamically on the current single selection for month or Year Slicers i.e. the maximun day for the selected month or selected year.

Regards,

[alexanderg]

Hi @v-yulgu-msft Thanks!!

Your Expression Works Fine!! Can you give me a feedback about how that Measure works? 

Thank you very much!!

[samdthompson]

Hi, I am assuming you are wanting the most recent day? If so, put a calculated column on your date filter which is something like:

MAXDAY=IF(date[calendar_date] = MAX(date[caldendar_date]), 1 , 0)

Then use that column as a filter to your visual. If your calendar is dynamically linked to say a range up to today then maybe use:

MAXDAY=IF(date[calendar_date] = TODAY()-1, 1, 0)

[alexanderg]

Hi @samdthompson Thanks for your reply,

I want to display in the chart, any expression only for the most recent day in the dataset considering dinamically the actual filters in the slicer, so not necessarily be this day, For example: suppose you have a dataset with values for periods of April, May and June. If I apply filter on a slicer for the month of April in the chart I want to see only the results associated of the maximum date of April. It must be the same behavior if another different month is selected. So the issue is: in the chart the total is the correct total associated with the maximum date, but the problem is that it shows me the results of the measure for the rest of the days that are not associated with the maximum date.

[samdthompson]

right. thats quite a different thing. have a read of this page. some frankenstiening of the solution used there will fix your problem:

http://community.powerbi.com/t5/Desktop/Dynamic-Line-and-Bar-Chart/m-p/39026#M14518