Calculate Last Date in a Date Slider Regardless of Other Filters

[Simon_KHFC]

Hi,

I am trying to create a measure to calculate the first date of a month based on the last date selected in a date slider - e.g. if the end of the slider shows 21/03/2023 I want to return 01/03/2023 (dates are in DD/MM/YYYY format). I also have other slicers in the dashboard such as region, customer name etc.

This is what I'm using currently;

Last Selected Date = STARTOFMONTH(LASTDATE('Table'[Date]))

This works fine as long as no other filters are applied to the visuals. If I apply another filter and there is no data available for that particular category, then the calculation returns the last date where data was available

E.g. Let's say I have the slider set to end on 21/03/2023 and the Region filter set to North East, but there were no sales in the North East in March 2023 - the measure is then giving me 01/02/2023 rather than 01/03/2023 as hoped, because February 2023 is the last month when there were sales in the North East.

Is there a way to change this measure so that it always returns the first of the month based on the last date selected in the slider regardless of other filters applied to the visuals?

Thanks in advance.

[MohammadLoran25 · Accepted solution]

Hi,

It seems that you only have a single table which contains all your data such as Date, Customer etc.

To have your issue fixed, you just need to create separate dimensions (date dimension, customer dimension etc.) and make realtionship between those and you main table.

In this way, once you filter a region or another field, it won't affect your date and actually does not filter it. So your measure would work as you expect.

If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.

Regards,

Loran

[Simon_KHFC]

Thank you Loran, that did the trick!