Calculate max date in a report with a slicer between date

[Anonymous]

Hi everyone and thanks for helpme.

I have a report with a slicer date (between), but in this page I have several graphics that only must show data for the max date of my slicer date:

If the max date in the slicer is 27/08/2020, these graphics only must show data for this day.

The metric that Im using, do a distinct count of the number of incident for the max date:

It seem that this metric run correctly in the donut and in the table, but not in the trend (chart), because calculate the max of the date but by field of the legend (CAUSE):

I found the error, because the char is sorted descending by this metric, but the chart seem not sorted, then I compared the data, and I checked for example that for Insurer 14, in the table show 4, but in the chart are 6, 1 storm, 3 other and 2 Hail, and so is because the metric is calculating the count distinct incident, using the max date by CAUSE using the range until 27/08/2020, and I would need the total max date showed in the slicer date like it was filtered just for the day 27/08/2020.

If I delete the field Cause of the legend in the chat object, the metric run correctly:

Now, the Ins_14 only show 4 incidents, but I would need show the colour for these 4 incident by CAUSE.

How could I do it? Thanks

[amitchandak · Accepted solution]

@Anonymous , change the var calculation like and check

maxx(allselected(MotorOderGermany),MotorOderGermany[Date])

always have date table and use that in such cases

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

@amitchandakThanks a lot, I applied your change and now the trend char show the data correctly!!