Need Distinct Values of "Month" in Slicer

[Anonymous]

Hello.

I have a Completion Date field which is in Date/Time format.

I then created a Completion Month field which I made = Completion Date then formatted Completion Month to "mmm yyyy".

Then I created a slicer for Completion Month, but I see multiple values of Completion Month.
How do I just show 1 value of "Nov 2023", 1 value of "Dec 2023", etc. in the slicer, versus many "Nov 2023", "Dec 2023", etc.

I tried to use DISTINCT function but got the error "A table of multiple values was supplied where a single value was expected".

Thank you.

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can use the formatted field as a slicer through the SUMMARIZE function.

Refer to the following dax:

slicer =
VAR _table2 =
    SUMMARIZE (
        'Table',
        [Completion  Date],
        "sli_Month", FORMAT ( 'Table'[Completion  Date], "mmmm-yyyy" )
    )
RETURN
    SUMMARIZE ( _table2, [sli_Month] )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

In the Query Editor or in a calculated column of the data model, remove time stamp.  Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number column.  Create a relationship (Many to One and Single) from the Data column of the Data Table to the Date column of the Calendar Table.  To any visual/slicer/filter, drag any date dimension from the Calendar Table.

[AmiraBedh]

Try the following :

Completion Month-Year Table = 
SUMMARIZE(
    YourTableName,
    YourTableName[Completion Date],
    "Completion Month-Year", FORMAT(YourTableName[Completion Date], "MMM YYYY")
)

[Anonymous]

Got an error. 

[AmiraBedh]

It is a calculated table and not a calculated column.

Meanwhile : 

Completion Month-Year Table = 
SUMMARIZE(
    YourTableName,
    ADDCOLUMNS(
        YourTableName,
        "Month-Year", FORMAT(YourTableName[Completion Date], "MMM YYYY")
    ),
    [Month-Year]
)

[Anonymous]

Here is the created table. I am seeing multiple month values;

[AmiraBedh]

I am sorry but I am confused what is your actual input and expected output ?