MIN value of a slicer based on a date in MM.YYYY format

[dgolovanova]

Good afternoon,

Please help with advice.

There is a field in the DATE format, contains only the month and year (2.2021, 8.2023, etc.).

The client want to use a slicer in the "between" style (they don't like the extra Timeline slicer).

Power BI automatically displays dates in the format dd.mm.yyyy, which means that the table value of 8.2023 corresponds to the value of 1.8.2023.

I use the MIN and MAX functions to determine the minimum and maximum value of the slicer.

I need that if I select a date other than the first day of the month, the selected month will be returned to me, not the next one.

For example, for 5.12.2021, I need to get the value 1.12.2021 instead of 1.1.2022.

Is it possible?

Thank you in advance.

[Jihwan_Kim · Accepted solution]

Hi,

In my opinion, if you want to use continuous date in the slicer, but if your fact table (sales table) date column is not continuous, please try using calenar dimension table, something like below.

Calendar = 
VAR _startdate =
    EOMONTH ( MIN ( Sales[Date] ), -1 ) + 1
VAR _enddate =
    EOMONTH ( MAX ( Sales[Date] ), 0 )
RETURN
    CALENDAR ( _startdate, _enddate )

Min date = 
VAR _selectedmonth =
    MONTH ( MIN ( 'Calendar'[Date] ) )
VAR _selectedyear =
    YEAR ( MIN ( 'Calendar'[Date] ) )
RETURN
    MINX (
        FILTER (
            Sales,
            YEAR ( Sales[Date] ) = _selectedyear
                && MONTH ( Sales[Date] ) = _selectedmonth
        ),
        Sales[Date]
    )

Max date = 
VAR _selectedmonth =
    MONTH ( MAX ( 'Calendar'[Date] ) )
VAR _selectedyear =
    YEAR ( MAX ( 'Calendar'[Date] ) )
RETURN
    MAXX(
        FILTER (
            Sales,
            YEAR ( Sales[Date] ) = _selectedyear
                && MONTH ( Sales[Date] ) = _selectedmonth
        ),
        Sales[Date]
    )

[dgolovanova]

Hello Jihwan,

Thanks for the reply, but this is not exactly what I need.
The problem is that my date field does not contain the day, only the month and year.
If I select the "Vertical List" slider type, the date is displayed the same way as in the table, i.e. in mm.yyyy format.

But if you select the "Between" slider type, the date is displayed in the format dd.mm.yyyy.
And if you select a start date greater than the first day of the month, then the minimum value of the slicer already returns the next month, not the current month.
In the following example "Max date" is ok, but "Min date" should be 12.2021 instead of 01.2022.

Can't attach the file 😞
Thanks again.
Regards

[Jihwan_Kim]

Hi,

Please provide your sample pbix file, and then I can try to look into it.

Please try using onedrive link or dropbox link to share the link of the sample file.

Thank you.

[dgolovanova]

https://1drv.ms/u/s!AgqdtoXYbFihgUvnd7xUOUyjwfMC?e=0ei9ql 

Thank you

[Jihwan_Kim · Accepted solution]

Hi,

In my opinion, if you want to use continuous date in the slicer, but if your fact table (sales table) date column is not continuous, please try using calenar dimension table, something like below.

Calendar = 
VAR _startdate =
    EOMONTH ( MIN ( Sales[Date] ), -1 ) + 1
VAR _enddate =
    EOMONTH ( MAX ( Sales[Date] ), 0 )
RETURN
    CALENDAR ( _startdate, _enddate )

Min date = 
VAR _selectedmonth =
    MONTH ( MIN ( 'Calendar'[Date] ) )
VAR _selectedyear =
    YEAR ( MIN ( 'Calendar'[Date] ) )
RETURN
    MINX (
        FILTER (
            Sales,
            YEAR ( Sales[Date] ) = _selectedyear
                && MONTH ( Sales[Date] ) = _selectedmonth
        ),
        Sales[Date]
    )

Max date = 
VAR _selectedmonth =
    MONTH ( MAX ( 'Calendar'[Date] ) )
VAR _selectedyear =
    YEAR ( MAX ( 'Calendar'[Date] ) )
RETURN
    MAXX(
        FILTER (
            Sales,
            YEAR ( Sales[Date] ) = _selectedyear
                && MONTH ( Sales[Date] ) = _selectedmonth
        ),
        Sales[Date]
    )

[dgolovanova · Accepted solution]

Good afternoon,
sorry for the late reply.

Everything works.

Thank you very much for help.
Regards

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

STARTOFMONTH function (DAX) - DAX | Microsoft Learn

Min Date start of month: = 
CALCULATETABLE (
    STARTOFMONTH ( 'Calendar'[Date] ),
    'Calendar'[Date] = MIN ( 'Calendar'[Date] )
)

Max Date start of month: = 
CALCULATETABLE (
    STARTOFMONTH ( 'Calendar'[Date] ),
    'Calendar'[Date] = MAX ( 'Calendar'[Date] )
)