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
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.
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.
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
Hi, its only a column defined as date field...
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" ?
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)