Slicer - How can I count selected months?

[posterme]

Hi Guys,

how can I or is it possible to count how many months I have selected in a Date-Slicer? I want to divide a value with the count of monts, e.g. earnings / selected months ... All variants do not show me the correct number of months - only distinctcount brings 12 months 😉

Thanks in advance

[ChrisHawker]

Months Counted in Slicer =

IF(

    ISCROSSFILTERED('Date Table'[Month]),

    DISTINCTCOUNT('Date Table'[Month])

)

[FCF]

hi

I have also tried this solution using "ISFILTERED" or "ISCROSSEDFILTERED" and both options only 

result in 0. I have two items selected in my slicer. Regards

[vega]

Count = 
IF(
    ISFILTERED(Months[Month]),
    COUNT(Months[Month]),
    0
 )

This will count if the months column is selected directly. If you want to account for the months column being filtered indirectly, use ISCROSSEDFILTERED instead of ISFILTERED.

[posterme]

Hi Vega,

thank you - unfortunately, the value is always "0" instead of, in this example, "5":

BR

[vega]

Can you post your Date table? How is the slicer being created? Like I said earlier, if the months are not being filtered directly, then you need to use ISCROSSFILTERED.

[posterme]

Hi,

I have tested both ISFILTERED and ISCROSSFILTERED - below you'll find the screenshot from the table field:

Slicer (Timeline) is configured only with the "Date" field:

Again - many thanks for your support 🙂

[YellOwBoyz]

There is certainly more optimize way to do it.

But for example you can duplicate your date column convert it into a type text, and concatenate the year and the month.

And then do a DISTINCTCOUNT of your new column.

Example of M query to get your year and month

Text.Range([Date], 6,4) & Text.Range([Date], 3, 2)

[cylix]

i too would like to know how to do this

[posterme]

Hi cylix,

till now I do not have a proper solution for that...

[Mathusan1997]

Hi, have you had any luck with this? I am tasked with doing something similar to this as well but the above solutions provided do not work. Please advise.

Thank you. @posterme 

[vega]

Does your date table have a months column?

[posterme]

Hi, its only a column defined as date field...

UPDATE:

And as I written in my first message,

with COUNT('TABLE'[Date].[Month]) the Value is 1096,

with DISTINCTCOUNT('TABLE'[Date].[Month]) it is 12 ...

But what is "1096" ? 🙂

[vega]

I believe 1096 is the number of dates in your date column